Xml query resulting in error in sql server

Question

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!

Kiran Hegde · Accepted Answer · 2014-07-02 09:51:09Z

1

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/.)

edited Jul 2, 2014 at 9:51

answered Jul 2, 2014 at 9:39

Kiran Hegde

3,6811 gold badge18 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user3449213 Over a year ago

Wow! could you pls Explain what was the error or mistake in my query? or how this works.. thanks in advance

Kiran Hegde Over a year ago

@user3449213, i have updated the answer with the problems in your query

user3449213 Over a year ago

thanks man! i changed your query to this

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

!

user3449213 Over a year ago

could you please also tell me what does [1] mean in your query?

Kiran Hegde Over a year ago

That is copied from your query in fact :). value() function expects to have a single node to convert it to the specific type. Since the xpath CompanyName/. can lead to multiple nodes, although we have only one, we need to tell to take the first one. so we need to mention [1]

Collectives™ on Stack Overflow

Xml query resulting in error in sql server

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related