Query data from a XML column in SQL Server

Question

I have serialized an object into a XML column in my SQL Server 2008 database.

The xml looks like this:

<SimpleUserData xmlns="http://schemas.datacontract.org/2004/07/MyProject.CustomFx.Web" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns:z="http://schemas.microsoft.com/2003/10/Serialization/" z:Id="1" z:Type="MyProject.CustomFx.Web.SimpleUserData" z:Assembly="MyProject.CustomFx.Web, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null">
   <BatchDescription z:Id="2">I want this value</BatchDescription>
   <BatchID>9c437c08-0f28-4c77-9a50-49a7a4e8930a</BatchID>
   <BatchTemplateID>And this value too</BatchTemplateID>
</SimpleUserData>

I want to write a query that shows the values inside the elements BatchDescription and BatchTemplateID.

My query as it stands now looks like this:

declare @xmlobj as xml = '<SimpleUserData xmlns="http://schemas.datacontract.org/2004/07/MyProject.CustomFx.Web" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns:z="http://schemas.microsoft.com/2003/10/Serialization/" z:Id="1" z:Type="MyProject.CustomFx.Web.SimpleUserData" z:Assembly="MyProject.CustomFx.Web, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null">
      <BatchDescription z:Id="2">I want this value</BatchDescription>
      <BatchID>9c437c08-0f28-4c77-9a50-49a7a4e8930a</BatchID>
      <BatchTemplateID>And this value too</BatchTemplateID>
    </SimpleUserData>';

select
  @xmlobj.query('
  declare namespace x="http://schemas.datacontract.org/2004/07/MyProject.CustomFx.Web";
(/x:SimpleUserData[1]/x:BatchDescription[1])
') as value1
 ,@xmlobj.query('  
declare namespace x="http://schemas.datacontract.org/2004/07/MyProject.CustomFx.Web";
(/x:SimpleUserData[1]/x:BatchTemplateID[1])
') as value2;

This comes close; it returns the entire tag and the value inside it.

How can I get just the values (i.e., "I want this value" and "And this value too")

Try to use .value instead of .query with a second parameter specifying the data type. — Mikael Eriksson
– Mikael Eriksson, Commented Dec 22, 2011 at 19:48
That works! Now I feel like a certified idiot :) If you make this comment an actual answer, I'll upvote & accept it. Thanks! — JosephStyons
– JosephStyons, Commented Dec 22, 2011 at 19:53

JosephStyons · Accepted Answer · 2011-12-23 15:47:38Z

3

.query will always return data type XML.

To query a value you should use ".value", and specify the data type as the second parameter.

Use this instead:

declare @xmlobj as xml = '<SimpleUserData xmlns="http://schemas.datacontract.org/2004/07/MyProject.CustomFx.Web" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns:z="http://schemas.microsoft.com/2003/10/Serialization/" z:Id="1" z:Type="MyProject.CustomFx.Web.SimpleUserData" z:Assembly="MyProject.CustomFx.Web, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null">
      <BatchDescription z:Id="2">I want this value</BatchDescription>
      <BatchID>9c437c08-0f28-4c77-9a50-49a7a4e8930a</BatchID>
      <BatchTemplateID>And this value too</BatchTemplateID>
    </SimpleUserData>';

WITH XMLNAMESPACES('http://schemas.datacontract.org/2004/07/MyProject.CustomFx.Web' as x)
select
  @xmlobj.value('(/x:SimpleUserData[1]/x:BatchDescription[1])','nvarchar(max)') as value1
 ,@xmlobj.value('(/x:SimpleUserData[1]/x:BatchTemplateID[1])','nvarchar(max)') as value2;

edited Dec 23, 2011 at 15:47

JosephStyons

59.1k64 gold badges169 silver badges240 bronze badges

answered Dec 22, 2011 at 19:59

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

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

2 Comments

marc_s Over a year ago

You could also use a WITH XMLNAMESPACES(<uri> AS x) intro statement to avoid having to declare those namespaces in each and every single @xmlobj.value(..) call

JosephStyons Over a year ago

Great suggestion - I've edited the answer to include that, and it is much more readable now.

Collectives™ on Stack Overflow

Query data from a XML column in SQL Server

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related