SQL - Read an XML node from a table field

Question

I am using SQL Server 2008. I have a field called RequestParameters in one of my SQL table called Requests with XML data. An example would be:

<RequestParameters xmlns="http://schemas.datacontract.org/2004/07/My.Name.Space" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns:z="http://schemas.microsoft.com/2003/10/Serialization/" z:Id="1">
  <Data z:Id="2" i:type="CheckoutRequest">
    <UserGuid>7ec38c44-5aa6-49e6-9fc7-25e9028f2148</UserGuid>
    <DefaultData i:nil="true" />
  </Data>
</RequestParameters>

I ultimately want to retrieve the value of UserGuid. For that, I am doing this:

SELECT RequestParameters.value('(/RequestParameters/Data/UserGuid)[0]', 'uniqueidentifier') as UserGuid
FROM Requests

However, the results I am seeing are all NULL. What am I doing wrong?

Mikael Eriksson · Accepted Answer · 2013-05-06 06:05:44Z

2

You have to specify the default namespace and use [1] instead of [0].

WITH XMLNAMESPACES(default 'http://schemas.datacontract.org/2004/07/My.Name.Space')
SELECT RequestParameters.value('(/RequestParameters/Data/UserGuid)[1]', 'uniqueidentifier') as UserGuid
FROM Requests;

SQL Fiddle

answered May 6, 2013 at 6:05

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

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

2 Comments

Mudassir Hasan Over a year ago

yup , specifying nanespace is necessary . +1

Mikael Eriksson Over a year ago

@Noobie [1] is used to specify the first instance of the value you want. You could use [2] to get the second instance if you had more than one UserGuid in your XML. [0] specifies the value before the first and well.. there is nothing before the first so you get nothing back.

Shafqat Masood · Accepted Answer · 2013-05-06 06:03:09Z

-1

  declare @XML xml

  set @XML = "<RequestParameters       xmlns="http://schemas.datacontract.org/2004/07/My.Name.Space"       xmlns:i="http://www.w3.org/2001/XMLSchema-instance"       xmlns:z="http://schemas.microsoft.com/2003/10/Serialization/" z:Id="1">
    <Data z:Id="2" i:type="CheckoutRequest">
      <UserGuid>7ec38c44-5aa6-49e6-9fc7-25e9028f2148</UserGuid>
      <DefaultData i:nil="true" />
    </Data>
  </RequestParameters>"

 select @XML.value('(/RequestParameters/Data /UserGuid)[1]', 'varchar')
  '

answered May 6, 2013 at 6:03

Shafqat Masood

2,5601 gold badge19 silver badges23 bronze badges

1 Comment

Mikael Eriksson Over a year ago

It could be because this answer does not work. Have you tried it? If not you could test it here

Collectives™ on Stack Overflow

SQL - Read an XML node from a table field

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related