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
| id | FooID | FooText |
| 1 | 34 | some text |
| 2 | 56 | some text |
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
| id | ValueByVariable |
| 1 | 22 |
| 2 | 14 |
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
| 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