selecting individual xml node using SQL

Question

I have a large XML note with many nodes.

is there a way that I can select only a single node and all of its contents from the larger XML?

i am using sql 2005

Solution

You should use the query() Method if you want to get a part of your XML.

declare @XML xml

set @XML = 
'
<root>
  <row1>
    <value>1</value>
  </row1>
  <row2>
    <value>2</value>
  </row2>
</root>
'

select @XML.query('/root/row2')

Result:

<row2>
  <value>2</value>
</row2>

If you want the value from a specific node you should use value() Method.

select @XML.value('(/root/row2/value)[1]', 'int')

Result:

2

Update:

If you want to shred your XML to multiple rows you use nodes() Method.

To get values:

declare @XML xml

set @XML = 
'
<root>
  <row>
    <value>1</value>
  </row>
  <row>
    <value>2</value>
  </row>
</root>
'

select T.N.value('value[1]', 'int')
from @XML.nodes('/root/row') as T(N)

Result:

(No column name)
1
2

To get the entire XML:

select T.N.query('.')
from @XML.nodes('/root/row') as T(N)

Result:

(No column name)
<row><value>1</value></row>
<row><value>2</value></row>

Comments

[kacalapy]

nice, what if each of the row nodes were just <row> and i wanted to get each row node sequentially?

[kacalapy]

i want the results (<row2><value>2</value></row2>) not an integer but i want to get row1 xml chunk, then row2... for all rows

[Mikael Eriksson]

@kacalapy - Updated answer with how you shred XML to multiple rows.

[Allan F]

Note that the XML for the nodes method example has <row> compared to <row1> of the first example higher up .. i.e. not the same XML ..