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