CODEWITHSUNDEEP
sql-serverxmlsql-server-2008
Tu Hoang
Tu Hoang

Reputation: 4712

SQL - Read an XML node from a table field

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?

Upvotes: 1

Views: 469

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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

Upvotes: 2

Shafqat Masood
Shafqat Masood

Reputation: 2570

  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')
  '

Upvotes: -1

Related Questions