Basic XML Examples

Here are some samples of various techniques for selecting from and into XML.

If you have XML in a column and need to select values using XQuery:


declare @tbl table (id int, xval xml);
insert @tbl values
    (1, '<root><items><foo id="34">some text</foo></items></root>')
    ,(2, '<root><items><foo id="56">more text</foo></items></root>');

select
    t.id
    ,x.value('@id', 'int') [FooID]
    ,x.value('.', 'varchar(max)') [FooText]
from @tbl t
outer apply t.xval.nodes('/root/items/foo') x(x);
go

Results:

id  FooID   FooText
1    34    some text
2    56    more text

Use a SQL variable to select from XML:

-- Pick the third item from each record
declare @idx int = 3;
declare @tbl table (id int, xval xml);
insert @tbl values
    (1, '<root><item>34</item><item>42</item><item>22</item></root>')
    ,(2, '<root><item>87</item><item>93</item><item>14</item></root>');

select
tbl.id
,x.value('.', 'int') [ValueByVariable]
from @tbl tbl
outer apply tbl.xval.nodes('root/item[sql:variable("@idx")]/text()') x(x)
go

Results:

id  ValueByVariable
1         22
2         14

Use a value from the query results as part of the XML query:

-- Pull the "nth" item value, where "n" is the id column
declare @tbl table (id int, xval xml);
insert @tbl values
    (1, '<root><item>34</item><item>42</item></root>')
    ,(2, '<root><item>87</item><item>93</item></root>');

select
tbl.id
,x.value('.', 'int') [ValueByColumn]
from @tbl tbl
outer apply tbl.xval.nodes('root/item[sql:column("tbl.id")]/text()') x(x)
go

Results:

id  ValueByColumn
1       34
2       93

Select from a table into XML:

if object_id('dbo.tbl') is not null
drop table dbo.tbl;
go
create table dbo.tbl (id int, name varchar(100));
insert dbo.tbl values (1, 'John'), (2, 'Jane');

-- Element is the table name, and columns become attributes
select * from dbo.tbl for xml auto;
/*
<dbo.tbl id="1" name="John" />
<dbo.tbl id="2" name="Jane" />
*/

-- Each element gets named "row"
select * from dbo.tbl for xml raw;
/*
<row id="1" name="John" />
<row id="2" name="Jane" />
*/

-- Define the root name and row name, then define the XML structure by the query column names
select id [@the-id], [name] [the-name] from dbo.tbl for xml path ('the-item'), root ('the-root');
/*
<the-root>
    <the-item the-id="1">
    <the-name>John</the-name>
    </the-item>
    <the-item the-id="2">
    <the-name>Jane</the-name>
    </the-item>
</the-root>
*/

go

if object_id('dbo.tbl') is not null
    drop table dbo.tbl;
go