CODEWITHSUNDEEP
sqlsql-serverxml
user3449213
user3449213

Reputation: 17

Xml query resulting in error in sql server

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!

Upvotes: 0

Views: 881

Answers (1)

Kiran Hegde
Kiran Hegde

Reputation: 3681

Change the query like this and try

SELECT
R.Node.value('(CustomerId/.)[1]','varchar(100)') AS CustomerID,
R.Node.value('(CompanyName/.)[1]','varchar(100)') AS CompanyName
FROM @XML.nodes('/ROOT/Customers')  R(Node)

First of all you have syntax error in your query. You have aliase to your resultant table as R(Node), then you cannot specifiy the path as R.Node(.). Secondly the path that you mentioned in your query is wrong (/Customers/CustomerId/.)

Upvotes: 1

Related Questions