I done with this but still no expected result out through this proc
DECLARE @nvarval NVARCHAR(max)
DECLARE @xmlval xml
DECLARE @tblxml TABLE(xmlfld XML)
SET @nvarval=''
SET @nvarval =@nvarval + '<ROOT>'
SET @nvarval =@nvarval + ' <Customers>'
SET @nvarval =@nvarval + ' <CustomerId>1111</CustomerId>'
SET @nvarval =@nvarval + ' <CompanyName>Sean Chai</CompanyName>'
SET @nvarval =@nvarval + ' <City>NY</City>'
SET @nvarval =@nvarval + ' </Customers>'
SET @nvarval =@nvarval + ' <Customers>'
SET @nvarval =@nvarval + ' <CustomerId>1112</CustomerId>'
SET @nvarval =@nvarval + ' <CompanyName>Tom Johnston</CompanyName>'
SET @nvarval =@nvarval + ' <City>LA</City>'
SET @nvarval =@nvarval + ' </Customers>'
SET @nvarval =@nvarval + ' <Customers>'
SET @nvarval =@nvarval + ' <CustomerId>1113</CustomerId>'
SET @nvarval =@nvarval + ' <CompanyName>Institute of Art</CompanyName>'
SET @nvarval =@nvarval + ' </Customers>'
SET @nvarval =@nvarval + '</ROOT>'
SET @xmlval=CAST(@nvarval AS XML)
INSERT INTO @tblxml VALUES(@xmlval )
--SELECT
--[xmlfld].value('(/ROOT//Customers//CustomerId//node())[1]', 'nvarchar(max)') as CustomerId,
--[xmlfld].value('(/ROOT//Customers//CompanyName//node())[1]', 'nvarchar(max)') as CompanyName,
--[xmlfld].value('(/ROOT//Customers//City//node())[1]', 'nvarchar(max)') as City
--FROM @tblxml
SELECT p.value('(././CustomerId)[1]', 'VARCHAR(8000)') as firstName,
p.value('(././CompanyName)[1]', 'VARCHAR(8000)') as lastName ,
p.value('(././City)[1]', 'VARCHAR(8000)') as city
FROM @tblxml CROSS APPLY xmlfld.nodes('/person') t(p)
It only result into one row.
Thanks in advace RPD