Parse / query XML using TSQL

Question

I've been fighting this for a while, seems I'm close but not quite there. I have a column in a database that looks like this:

<document>
<items>
<item name="one">one is the first number</item>
<item name="two">two is the second number</item>
</items>
</document>

In this example I need to query and return 'two is the second number'. I'd also like to do this without creating a temp table. Currently I have:

create table #test (item1 xml)
insert into #test (item1) 
values ('<document> <items> <item name="one">one is the first number</item> <item name="two">two is the second number</item> </items> </document>')

select item1.value('(/document/items/item)[2]', 'nvarchar(max)') from #test
select item1.query('/document/items/item[@name="two"]') from #test

The first select returns the correct value but I need to know that it's the 2nd 'index' The second returns what I want but it returns the entire node two..

What am I missing? And, is there a simple way to use the XML without converting to a temp table?

Mikael Eriksson · Accepted Answer · 2012-01-13 09:58:29Z

I'd also like to do this without creating a temp table

You can use a variable with datatype XML.

declare @xml xml

set @xml = 
'<document>
  <items>
    <item name="one">one is the first number</item>
    <item name="two">two is the second number</item>
  </items>
</document>'

select @xml.value('(/document/items/item[@name="two"])[1]', 'nvarchar(max)')

Or you can cast your string to XML in the query.

select cast(
            '<document>
              <items>
                <item name="one">one is the first number</item>
                <item name="two">two is the second number</item>
              </items>
            </document>' as xml
           ).value('(/document/items/item[@name="two"])[1]', 'nvarchar(max)')

Your first query uses .value() which is correct and your second query has the correct XQuery expression. When using .value() you need to use a XQuery expression that returns a single value. This will give you all item nodes where @name is two /document/items/item[@name="two"]). Adding [1] at the end makes sure that you will only get the first occurrence in the XML where @name is two.

AakashM · Accepted Answer · 2012-01-13 09:53:04Z

0

(First off, rather than a temp table, you can use a variable of type xml, as I do below. Such variables can be assigned directly from string literals)

So I think you mean you want the text value of the item node with name two, in which case you just need to include the appropriate condition in the xpath you use in your value() call:

DECLARE @x xml

SET @x = '<document> <items> <item name="one">one is the first number</item> 
     <item name="two">two is the second number</item> </items> </document>'

SELECT @x.value('(/document/items/item[@name="two"])[1]', 'nvarchar(max)')

gives

--------------------------------------------------------------
two is the second number

(1 row(s) affected)

answered Jan 13, 2012 at 9:53

AakashM

63.5k17 gold badges154 silver badges190 bronze badges

Collectives™ on Stack Overflow

Parse / query XML using TSQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related