This is my xml query
declare @XML xml
set @XML=
'<ROOT>
<Customers>
<CustomerId>1111</CustomerId>
<CompanyName>Sean Chai</CompanyName>
<City>NY</City>
</Customers>
<Customers>
<CustomerId>1112</CustomerId>
<CompanyName>Tom Johnston</CompanyName>
<City>LA</City>
</Customers>
<Customers>
<CustomerId>1113</CustomerId>
<CompanyName>Institute of Art</CompanyName>
</Customers>
</ROOT>';
SELECT
R.Node('.').value('(/Customers/CustomerId/.)[1]','varchar(100)') AS CustomerID,
R.Node('.').value('(/Customers/CompanyName/.)[1]','varchar(100)') AS CompanyName
FROM @XML.nodes('/ROOT/Customers') R(Node)
But its giving error:
Msg 4121, Level 16, State 1, Line 20
Cannot find either column "R" or the user-defined function or aggregate "R.Node", or the name is ambiguous.
i searched but couldn't get R.node Thing pls somebody guide me onthis !
and i'm not understanding @XML.nodes('/ROOT/Customers') R(Node) ?
what is its use in query!
No comments:
Post a Comment