Basic XML Examples in SQL Server

Select values from an XML column

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

Select from XML using a variable

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

Use value from query results as part of 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

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