tags:

views:

55

answers:

0

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