Extracts data from an XML document and returns it as a SQL table.
xmltable_expression ::= xmltable
( row_pattern passing xml_argument
columns column_definitions
options_parameter)
row_pattern ::= character_string_literal
xml_argument ::= xml_expression
column_definitions ::=
column_definition [ { , column_definition } ]
column_definition ::=
ordinality_column | regular_column
ordinality_column ::= column_name datatype for ordinality
regular_column ::=
column_name datatype [ default literal ] [null | not null]
[ path column_pattern ]
column_pattern ::= character_string_literal
options_parameter ::=[,] option option_string
options_string ::= basic_string_expression
Derived table syntax Returns a SQL table from within a SQL from clause.
from_clause ::= from table_reference [, table_reference]... table_reference ::= table_view_name | ANSI_join |derived_table table_view_name::=See the select command in Reference Manual Volume 2, "Commands". ANSI_join::=See the select command in Reference Manual Volume 2, "Commands". derived_table ::= (subquery) as table_name [ (column_name [, column_name]...) xmltable_expression as table_name
xmltable as derived tables This example shows a simple xmltable() call, returning a derived table.
select * from xmltable('/doc/item'
passing '<doc><item><id>1</id><name>Box</name></item>'
+'<item><id>2</lid><name>Jar</name></item></doc>'
columns id int path 'id',name varchar(20) path 'name') as items_table
id name
----- -----
1 Box
2 Jar
(2 rows affected)
The syntax of derived tables requires you to specify a table name (items_table), even if you do not reference it. For example, this example is incorrect.
select * from xmltable ('/doc/item'
passing '<doc><item><id>1</id><name>Box</name></item>'
+'<item><id>2</id><name>Jar</name></item></doc>'
columns id int path 'id', name varchar(20) path 'name')
-------------
Msg 102 Level 15, State 1:
Incorrect syntax near ')'
Simple document reference examples In document references, the argument following passing is the input XML document. In this example the document is specified as a character string literal.
select * from xmltable ('/doc/item'
passing '<doc><item><id>1</id><name>Box</name></item>'
+'<item><id>2</id><name>Jar</name></item></doc>'
columns id int path 'id', name varchar(20) path 'name') as items_table
id name
------- ------
1 Box
2 Jar
(2 rows affected)
This example shows storing the document in a T-SQL variable, and referencing that variable in the xmltable() call.
declare @doc varchar(16384) set @doc='<doc><item><id>1</id></name>Box</name></item>' +'<item><id>2</id><name>Jar</name></item></doc>' select * from xmltable(/doc/item' passing @doc columns id int path 'id', name varchar(20) path 'name') as items_table id name ---- ----- 1 Box 2 Jar (2 rows affected)
To store the document in a table and reference it with a scalar subquery:
select 100 as doc_id, '<doc><item><id>1</id><name>Box</name></item><item><id>2</id>
<name>Jar</name> </item></doc>'as doc
into #sample_docs
select* from xmltable('/doc/item'
passing(select doc from #sample_docs where doc_id=100)
columns id int path 'id',name varchar(20) path 'name') as items_table
id name
------- -----
1 Box
2 Jar
(2 rows affected)
Row patterns The first argument in the xmltable call,
the row-pattern (’/doc/item’) is
an XPath query expression whose result is a sequence of elements
from the specified document. The xmltable call
returns a table with one row for each element in the sequence.
If the row pattern returns an empty sequence, the result is an empty table:
select * from xmltable ('//item_entry'
passing '<doc><item><id>1</id><name>Box</name></item>'
+'<item><id>2</id><name>Jar</name></item></doc>'
columns id int path 'id',
name varchar(20) path 'name') as items_table
id name
------- ------
(0 rows affected)
The row pattern expression cannot be an XPath function:
select * from xmltable ('/doc/item/tolower()'
passing '<doc><item><id>1</id><name>Box</name></item>'
+'<item><id>2</id><name>Jar</name></item></doc>'
columns id int path 'id',
name varchar(20) path 'name') as items_table
id name
--- ------
Msg 14825, Level 16, State 0:
Line1:
XPath function call must be at leaf level.
Column patterns The arguments following the columns keyword is the list of column definitions. Each column definition specifies a column name and datatype, as in create table, and a path, called the column pattern. The column-pattern is an XPath query expression that applies to an element of the sequence returned by the row-pattern, to extract the data for a column of the result table.
When the data for a column is contained in an XML attribute, specify the column pattern using "@" to reference an attribute. For example:
select * from xmltable ('/doc/item'
passing '<doc><item id>"1"><name>Box</name></item>'
+'<item id="2">/id><name><Jar</name></item></doc>'
columns id int path '@id', name varchar(20)) as items_table
id name
--------------------
1 Box
2 Jar
(2 rows affected)
Default column patterns A column-pattern is commonly the same as the specified column_name, for example name. In this case, omitting the column-pattern results in defaulting to the column_name:
select * from xmltable ('/doc/item'
passing '<doc><item><id>1</id><name>Box</name></item>'
+'<item><id>2</id><name>Jar</name></item></doc>'
columns id int, name varchar(20)) as items_table
id name
--------------------
1 Box
2 Jar
(2 rows affected)
If you want a column pattern to default to the column name, in a column whose value is in an XML attribute, use a quoted identifier. You must then quote such identifiers when you reference them in the results:
set quoted_identifier on
select "@id", name from xmltable ('/doc/item'
passing '<doc><item id="1"><name>Box</name></item>'
+'<item id="2"><name>Jar</name></item></doc>'
columns "@id" int, name varchar(20)) as items_table
@id name
--------------------
1 Box
2 Jar
(2 rows affected)
You can also use quoted identifiers to specify column names as default column patterns, using column names that are more complex XPath expressions. For example:
set quoted_identifier on
select "@id", "name/short", "name/full" from xmltable ('/doc/item'
passing '<doc><item id="1"><name><short>Box</short>
<full>Box, packing, moisture resistant, plain</full>
</name></item>'
+'<item id="2"><name><short>Jar</short>
<full>Jar, lidded, heavy duty</full>
</name></item></doc>'
columns "@id" int, "name/short" varchar(20), "name/full" varchar(50))
as items_table
@id name/short name/full
--------------------
1 Box Box, packing, moisture resistant, plain
2 Jar Jar, lidded, heavy duty
(2 rows affected)
Implicit text() This example demonstrates the function text(), which is generally implicit in the column pattern. text() removes XML element tags. For example, this XPath query returns the selected element with the XML markup:
1> declare @doc varchar(16384) 2> set @doc= ’<doc><item><id>1</id></name>Box</name></item>’ +<’item><id>2</id><name>Jar</name></item></doc>’ 3> select xmlextract(’/doc/item[2]/name’, @doc) ------------ <name>Jar</name>
Adding text() to the XPath query removes the XML tags:
1> declare @doc varchar(16384) 2> set @doc= ’<doc><item><id>1</id></name>Box</name></item>’ +<’item><id>2</id><name>Jar</name></item></doc>’ 3> select xmlextract(’/doc/item[2]/name/text()’, @doc) ------------ Jar
text() is implicit in most column patterns. This example does not specify text() in the column pattern for either the id or name column:
select * from xmltable ('/doc/item'
passing '<doc><item><id>1</id><name>Box</name></item>'
+'<item><id>2</id><name>Jar</name></item></doc>'
columns id int path 'id', name varchar(20) path 'name') as items_table
id name
--- -------
1 Box
2 Jar
(2 rows affected
)
Datatype conversion You can derive column values in datatype conversions by applying an implicit SQL convert statement to the data extracted from the column pattern. For example:
select * from xmltable ('/emps/emp'
passing '<emps>
<emp><id>1</id><salary>123.45</salary><hired>1/2/2003</hired></emp'
+'<emp><id>2</id><salary>234.56</salary><hired>2/3/2004</hired></emp>'
+</emps>'
columns id int path 'id', salary dec(5,2), hired date)
as items_table
id salary hired
------- --------- --------
1 123.45 Jan 2, 2003
2 234.56 Feb 3, 2004
(2 rows affected)
The extracted XML data for the column must be convertible to the column datatype, or an exception is raised:
select * from xmltable ('/emps/emp'
passing '<emps>
+<emp><id>1</id><salary>123.45</salary><hired>1/2/2003</hired></emp'
+'<emp><id>2</id><salary>234.56 C$</salary><hired>2/3/2004</hired></emp>'
+</emps>'
columns id int path 'id', salary dec(5,2), hired date)
as items_table
----------------------
Msg 14841, Level 16, State 3: Line 1: XMLTABLE:Failed to convert column pattern result to DECML for column 1.
To handle XML data whose format is not suitable for a SQL convert function, extract the data to a string column (varchar, text, image, java.lang.String)
select * from xmltable ('/emps/emp'
passing '<emps>
+<emp><id>1</id><salary>123.45</salary><hired>1/2/2003</hired></emp'
+'<emp><id>2</id><salary>234.56 </salary><hired>2/3/2004</hired></emp>'
+</emps>'
columns id int, salary varchar(20), hired date)
as items_table
id salary hired
---------------------------------
1 123.45 Jan 2, 2003
2 234.56 Feb 3, 2004
(2 rows affected)
Ordinality columns The order of elements in XML documents can be significant.
Elements are sometimes ordered by the value of contained elements. In this example, the <item> elements are ordered by the value of the contained <id> elements.
<doc> <item><id>1<name>Box</name></item>' <item><id>2<name>Jar</name></item>' </doc>
You can also order elements in an arbitrary but significant manner. In the following example, the order of the <item> elements is based on no values, but may reflect a priority ordering: first in, first out. Such an ordering can be significant for the application of the data.
<doc> <item><id>25<name>Box</name></item>' <item><id>15<name>Jar</name></item>' </doc>
You can use an ordinality_column in xmltable to record the ordering of elements in the input XML document:
declare @doc varchar(16384)
set @doc = '<doc><item><id>25<name>Box</name></item>'
+'<item><id>15</id><name>Jar</name></item></doc'
select * from xmltable('/doc/item' passing @doc
columns item_order int for ordinality,
id int path 'id',
name varchar(20) path 'name') as items_table
order by item_order
item_order id name
---------- --- ----
1 25 Box
2 15 Jar
(2 rows affected)
-----------
Without the for ordinality clause and the item_order column, there is nothing in the id and name columns that indicates that the row of id 25 precedes the row of id 15. The for ordinality clause provides a way to make sure that the ordering of the output SQL rows is the same as the ordering of the elements in the input XML document.
The datatype of an ordinality column can be any fixed numeric datatype: int, tinyint, bigint, numeric, or decimal. numeric and decimal must have a scale of 0. An ordinality column cannot be real or float.
Null values If a column pattern returns an empty result, the action taken depends
on the default and {null | not
null} clauses.
This example omits the <name> element from the second <item>. The name column allows names by default.
select * from xmltable ('//item'
passing '<doc><item><id>1</id><name>Box</name></item>'
+'<item><id>2</id></item></doc>'
columns id int path 'id', name varchar(20), path 'name')
as items_table
-------------------------
id name
------------------
1 Box
2 NULL
(2 rows affected)
This example omits the <name> element from the second <item>, and specifies not null for the name column:
select * from xmltable ('/doc/item'
passing '<doc><item><id>1</id><name>Box</name></item>'
+'<item><id>2</id></item></doc>'
columns id int path 'id', name varchar(20) not null path 'name')
as items_table
-------------------------
Msg 14847, Level 16, State 1:
Line 1:
XMLTABLE column 0, does not allow null values.
This example adds a default clause to the name column, and omits the <name> elements from the second <item>.
select * from xmltable ('/doc/item'
passing '<doc><item><id>1</id><name>Box</name></item>'
+'<item><id>2</id></item></doc>'
columns id int path 'id' name varchar(20) default '***' path 'name')
as items_table
id name
------------------
1 Box
2 ***
(2 rows affected)
Context of xmltable calls These examples show SQL commands in which you can use an xmltable call in a derived table expression.
select – You can use xmltable() in a simple select statement:
select * from xmltable ('/doc/item'
passing '<doc><item><id>1</id><name>Box</name></item>'
+'<item><id>2</id><name>Jar</name></item></doc>'
columns id int path 'id'
name varchar(20) path 'name')as items_table
id name
-- ----
1 Box
2 Jar
(2 rows affected)
View definition – Specify select using xmltable in a view definition.This example stores a document in a table and references that stored document in a create view statement, using xmltable to extract data from the table:
select 100 as doc_id,
'<doc><item><id>1</id><name>Box</name></item>'
+'<item><id>2</id><name>Jar</name></item></doc>' as doc
into sample_docs
create view items_table as
select * from xmltable('/doc/item'
passing (select doc from sample_docs where doc_id=100)
columns id int path 'id'
name varchar(20) path 'name')as xml_extract
id name
------------------
1 Box
2 Jar
(2 rows affected)
Cursor declaration – Specify select using xmltable to declare a cursor:
declare cursor C for
select * from xmltable ('/doc/item'
passing (select doc from sample_docs where id=100)
columns id int path 'id'
name varchar(20) path 'name')as items_table
declare @idvar int
declare @namevar varchar(20)
open C
while @@sqlstatus=0
begin
fetch C into @idvar, @namevar
print 'ID "%1!" NAME"%2!"', @idvar, @namevar
end
-------------------------
ID "1" NAME "Box"
ID "2" NAME "Jar"
(2 rows affected)
In applications that require multiple actions for each generated row, such as executing update, insert, or delete from other tables, based on the data in each generated row, process an xmltable result with a cursor loop. Alternatively, you can store the xmltable result in a temporary table and process that table with a cursor loop.
select into – Specify select using xmltable in select into:
select * into #extracted_table
from xmltable('/doc/item'
passing (select doc from sample_docs where doc_id=100
columns id int path 'id'
name varchar(20) path 'name') as items_table
select * from #extracted_table
id name
---- ------------
1 Box
2 Jar
insert – Specify select using xmltable in an insert command:
create table #extracted_data (idcol int, namecol varchar(20))
insert into #extracted_data
select * into #extracted_table from xmltable('/doc/item'
passing (select doc from sample_docs where doc_id=100
columns id int path 'id', name varchar(20) path 'name')as items_table
select * from extracted_data
id name
----- ---------
1 Box
2 Jar
(2 rows affected)
Scalar subquery – Specify select using xmltable in a scalar subquery. xmltable returns a SQL table, so the scalar subquery must perform either an aggregation or a selection to return a single row and column for the scalar subquery result.
declare @idvar int
set @idvar = 2
select @idvar,
(select name from xmltable ('/doc/item'
passing(select doc from sample_docs where doc_id=100
columns id int path 'id',name varchar(20) path 'name') as item_table
where items_table.id=@idvar)
----------------------------
2 Jar
(1 rows affected)
Joins – Join an xmltable result with other tables, using either comma-list joins or outer joins:
create table prices (id int, price decimal (5,2))
insert into prices values(1,123.45)
insert into prices values (2,234.56)
select prices.id,extracted_table.name, prices.price
from prices,(select * from xmltable('/doc/item'
passing (select doc from sample_docs where doc_id=100
columns id int path 'id', name varchar(20) path 'name')as a) as
extracted_table
where prices.id=extracted_table.id
id name price
----- ---------
1 Box 123.45
2 Jar 234.56
(2 rows affected)
Processing tables of documents You can apply xmltable() to the XML document in each row of a table of XML documents. For example, the next example creates a table containing two columns:
The pub_id of one of the three publishers in the pubs2_publishers table.
An XML document containing the title and price of each document published by that publisher. To reduce the size of the example table only titles whose price is greater than $15.00 are included:
create table high_priced_titles (pub_id char(4), titles varchar (1000)) insert into high_priced_titles select p.pub_id, (select title_id, price from pubs2..titles t,pubs2..publishers p where price> 15 and t.pub_id=p.pub_id for xml option 'tablename=expensive_titles, rowname=title') returns varchar(1000))as titles from pubs2..publishers p select * from high_priced_titles -------------- pub_id titles ------ ------------------------------------------------- 0736 <expensive_titles> <title> <title_id>PS3333</title_id> <price>19.99</price></title> </expensive_titles> 0877 <expensive_titles> <title> <title_id>MC2222</title_id> <price>19.99</price></title> <title> <title_id>PS1372</title_id> <price>21.59</price></title> <title> <title_id>TC3218</title_id> <price>20.95</price></title> </expensive_titles> 01389 <expensive_titles> <title> <title_id>BU1032</title_id> <price>19.99</price></title> <title> <title_id>BU7832</title_id> <price>19.99</price></title> <title> <title_id>PC1035</title_id> <price>22.95</price></title> <title> <title_id>PC8888</title_id> <price>20.00</price></title> </expensive_titles> (3 rows affected)
Use xmltable in a scalar subquery to process the XML document in each row, as a SQL table. For example, list the maximum title price for each publisher:
select pub_id
(select max(price)
from xmltable('//title'passing hpt.titles
columns title_id char(4), price money)
as extracted_titles, high_priced_titles hpt) as max_price
from high_priced_titles hpt
--------------
pubid max_price
-----------------------
0736 19.99
0877 21.59
1389 22.95
This high_priced_titles table is essentially hierarchic: each row is an intermediate node, which contains, in its title column, a leaf node for each title element in the XML document. high_priced_titles has three rows.
You can flatten that hierarchy, producing a table with a row for each title element. To flatten the data in the titles column and produce a table, high_priced_titles_flattened, which has eight rows (one for each of the titles/title elements), use one of the following solutions.
Solution 1 You can produce high_priced_titles_flattened by using a loop that processes high_priced_titles, and applies xmltable to the titles document in each row. In the example below, notice the from clause:
from(select @pub_id_var)as ppp,
xmltable('//title' passing @titles_var
columns title_id char(6),price money)as ttt
The variables @pub_id_var and @titles_var are the pub_id and titles columns from the current row of high_priced_titles. The from clause joins two derived tables:
(select @pub_id_var)
as ppp
This is a table with one row and one column, containing the pub_id.
xmltable(...) as ttt
This generates a table with a row for each title element in the titles document of the current high_priced_titles row.
To flatten the hierarchy, join these two derived tables, which appends the pub_id column to each row generated from the titles column:
create table high_priced_titles_flattened_1 (pub_id char(4), title_id(char(6), price money)
declare C cursor for select * from high_priced_titles
declare @pub_id_var char(4)
declare @titles_var char(1000)
open C
while @@sqlstatus =0
begin
fetch C into @pub_id_var, @titles_var
insert into high_priced_titles_flattened_1
select *
from (select @pub_id_var) as ppp,(col1),
xmltable('//title' passing @titles_var
columns title_id char (6), price money) as ttt
end
select * from high_priced_titles_flattened_1
pub_id title_id price
------ -------------------------------------------------
0736 PS3333 19.99
0877 MC2222 19.99
0877 PS1372 21.59
0877 TC3218 20.95
1389 BU1032 20.95
1389 BU7832 19.99
1389 PC1035 19.99
1389 PC8888 20.00
Solution 2 You can also generate the high_priced_titles table using a special join.
This example joins two tables: high_priced_titles as hpt, and the table generated by xmltable. The passing argument of xmltable references the preceding hpt table. Normally, it is illegal to reference a table in a from clause, in a derived table expression within the same from clause. However, xmltable is allowed to reference other tables in the same from clause, as long as these tables precede the xmltable call in the same from clause.
select hpt.pub_id, extracted_titles.*
into high_priced_titles_flattened_3
from high_priced_titles as hpt,
xmltable('//title'
passing htp.titles,
columns
title_id char(6)
price money)as extracted_titles
pub_id title_id price
--------- -------------- ---------
0736 PS3333 19.99
0877 MC2222 19.99
0877 PS1372 21.59
0877 TC3218 20.95
1389 BU1032 20.95
1389 BU7832 19.99
1389 PC1035 19.99
1389 PC8888 20.00
xmltable is a built-in, table-valued function.
The result type of an xmltable expression is a SQL table, whose column names and their datatypes are specified by column_definitions.
These keywords are associated with xmltable:
Reserved: for, option
Not reserved: columns, ordinality, passing, path, xmltable
The expressions in the arguments of an xmltable call can reference the column names of preceding tables in the from clause containing the xmltable call. Only tables that precede the xmltable call can be referenced. Such a reference, to a column of a preceding table in the same from clause, is called a lateral reference. For example:
select * from T1, xmltable(...passing T1.C1...) as XT2, xmltable(...passing XT2.C2...)as XT3
The reference to T1.C1 in the first xmltable call is a lateral reference to column C1 of table T1. The reference to XT2.C2 in the second xmltable call is a lateral reference to column C2 of the table generated by the first xmltable call.
You cannot use xmltable in the from clause of an update or delete statement. For example, the following statement fails:
update T set T.C=... from T,xmltable(...) where...
You cannot update the SQL table returned by an xmltable expression.
Datatypes in regular_columns can be any SQL datatype.
The literal following a default in a regular_column must be assignable to the datatype of the column.
There can be no more than one ordinality_column; the datatype specified for this variable must be integer, smallint, tiny int, decimal, or numeric. decimal and numeric must have a scale of zero.
An ordinality_column,
if one exists, is not nullable. The nullable property of other columns
is specified by the {null | not
null} clause. The default is null.
This default is different from the default value of create
table.
The current setting of set quoted_identifier applies to the clauses of an xmltable expression. For example,
If set quoted_identifier is on, column names can be quoted identifiers, and string literals in row_pattern, column_pattern, and default literals must be surrounded with single quotation marks.
If set quoted_identifier is off, column names cannot be quoted identifiers, and string literals in row_pattern, column_pattern, and default literals can be surrounded with either single or double quotation marks.
The general format of the option_string is described in the section “option_strings: general format,”.
xmltable row and column patterns xmltable row and column patterns are allowed to be only simple paths. Simple paths in XPath consist only of forward traversals using '/' and element/attribute names.
If the row_pattern does not begin at the root level of the document specified by xml_argument, an exception is raised. The row pattern must begin at the root of the XML document.
If the row_pattern specifies an XML function, an exception is raised. The row pattern must not specify an XML function.
If a column_definition does not specify a path, then the default column_pattern is the column_name of the column definition. This default is subject to the case sensitivity of the server. For example, consider this statement:
select * from xmltable(...columns name varchar(30),...)
If the server is case-insensitive, this is equivalent to the following:
select * from xmltable(...columns name varchar(30) path 'name',...)
If the server is case sensitive, the first statement is equivalent to:
select * from xmltable (...columns name varchar(30)path 'NAME',...)
The result value of an xmltable expression is a T-SQL table RT, defined as follows:
RT has a row for each element in the XML sequence that results from applying the row_pattern to the xml_argument.
The rows of RT have a column for each column_definition, with the column_name and datatype specified in the column_definition.
If a column_definition is a ordinality_column, its value for the Nth row is the integer N.
If a column_definition is a regular_column, its value for the Nth row corresponds to the following:
Let XVAL be the result of applying this XPath expression to the xml_argument:
(row_pattern[N])/column_pattern/text()
If XVAL is empty, and the column_definition contains a default clause, the value of the column is that default value.
If XVAL is empty and the column_definition specifies not null, an exception is raised.
Otherwise, the value of the column is the null value.
If XVAL is not empty, and the datatype of the column is char, varchar, text, unitext, unichar, univarchar, or java.lang.String, de-entitize XVAL.
The value of the column is the result of:
convert(datatype,XVAL)
For a sample application using xmltable, see Appendix D, “Sample Application for xmltable(),”in XML Services.
