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_reference | variable_reference 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
is an expression, column reference, or variable, referring to an XML document.
is a reserved XML keyword.
is a non-reserved XML keyword.
is a non-reserved XML keyword.
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.
is a non-reserved XML keyword.
is the user-specified name of the column.
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. If the column_pattern is omitted, the column_pattern defaults to the column_name.
is a column of datatypes integer, smallint, tinyint, decimal, or numeric, which indicates ordering of the elements in the input XML document.
is any column that is not an ordinality column.
is a parenthesized subquery specified in the from clause of a SQL query.
is a reserved XML keyword.
is an option_string, defined in XML Services, and a reserved XML keyword.
Shows a simple xmltable call with the document 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)
Stores the document in a Transact-SQL variable, and references 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)
Stores the document in a table and references it with a 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)
If a row pattern returns an empty sequence, the result is an empty table:
select * from xmltable ('/doc/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 arguments following the columns keyword comprise 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.
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"><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)
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)
The function text is implicit in 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)
Applying an implicit SQL converst statement to the data extracted from the column pattern, derives column values in datatype conversions.
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)
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</id><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 orders the output SQL rows 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.
Omits the <name> element from the second <item>. The name column allows names to be NULL by default.
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) path 'name') as items_table id name ------------------ 1 Box 2 NULL (2 rows affected)
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 id name ----- -------- 1 Box Msg 14847, Level 16, State 1: Line 1: XMLTABLE column 0, does not allow null values.
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)
Shows SQL commands in which you can use an xmltable call in a derived table expression. This example uses 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)
Uses xmltable in a view definition. It 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 select * from items_table id name ------------------ 1 Box 2 Jar (2 rows affected)
Uses xmltable in a cursor:
declare C cursor 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 go 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, you can process an xmltable result with a cursor loop. Alternatively, store the xmltable result in a temporary table and process that table with a cursor loop.
This example uses 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
Uses xmltable in an insert command:
create table #extracted_data (idcol int, namecol varchar(20)) insert into #extracted_data 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 select * from #extracted_data idcol namecol ----- --------- 1 Box 2 Jar (2 rows affected)
Uses xmltable in a subquery. xmltable returns a SQL table, so the subquery must perform either an aggregation or a selection to return a single row and column for the 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 an xmltable result with other tables, using either multiple table joins in the from clause, 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)
Uses xmltable, with a lateral reference to a column existing in a preceding table in the same from clause as xmltable:
create table deptab (col1 int, col2 image) insert deptab values (1, '<dept> <dept-id>1</dept-id> <dept-name>Finance</dept-name> <employees> <emp><name>John</name><id>e11</id></emp> <emp><name>Bela</name><id>e12</id></emp> <emp><name>James</name><id>e13</id></emp> </employees> </dept>') insert deptab values (2, '<dept> <dept-id>2</dept-id> <dept-name>Engineering</dept-name> <employees> <emp><name>Tom</name><id>e21</id></emp> <emp><name>Jeff</name><id>e22</id></emp> <emp><name>Mary</name><id>e23</id></emp> </employees> </dept>') select id, empname from deptab, xmltable ('/dept/employees/emp' passing deptab.co12 columns empname varchar (8) path 'name', id varchar (8) path 'id') as sample_tab id empname --------- ----------- e11 John e12 Bela e13 James e21 Tom e22 Jeff e23 Mary (6 rows affected)
xmltable is a built-in, table-valued function.
The syntax of derived tables requires you to specify a table name, even if you do not reference it. Therefore, each xmltable expression must also specify a table name.
The argument following passing is the input XML document.
The result type of an xmltable expression is a SQL table, whose column names and their datatypes are specified by column_definitions.
To process documents, you can apply xmltable to the XML document in each row of a table of XML documents.
These keywords are associated with xmltable:
Reserved – for, option, xmltable, path
Not reserved – columns, ordinality, passing
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 xmltable(...) as T where...
Datatypes in regular_columns can be of any SQL datatype.
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).
The extracted XML data for the column must be convertible to the column datatype, or an exception is raised.
If a column pattern returns an empty result, the
action taken depends on the default
and {null | not
null}
clauses.
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, tinyint, decimal, or numeric. decimal and numeric must have a scale of zero.
An ordinality_column, if one exists, is not nullable.
This default is different from the default value of create table.
The nullable property of other columns is specified
by the {null | not null}
clause.
The default is null.
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 “option_strings: general format,” in XML Services, Adaptive Server 15.0.
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.
The row pattern expression cannot contain an XPath function.
A column pattern must be a relative path.
If the row_pattern specifies an XML function, an exception is raised. The row pattern cannot specify an XML function.
If a column_definition does not specify a path, 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)