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?

Answer 1

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.

Answer 2

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