xa_gtrid  year

Chapter 2: Transact-SQL Functions

xmltable

Description

Extracts data from an XML document and returns it as a SQL table.

Syntax

 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

Parameters

xml_argument

is an expression, column reference, or variable, referring to an XML document.

for

is a reserved XML keyword.

ordinality

is a non-reserved XML keyword.

passing

is a non-reserved XML keyword.

row_pattern

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.

columns

is a non-reserved XML keyword.

column_name

is the user-specified name of the column.

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. If the column_pattern is omitted, the column_pattern defaults to the column_name.

ordinality_column

is a column of datatypes integer, smallint, tinyint, decimal, or numeric, which indicates ordering of the elements in the input XML document.

regular_column

is any column that is not an ordinality column.

derived_table

is a parenthesized subquery specified in the from clause of a SQL query.

path

is a reserved XML keyword.

option

is an option_string, defined in XML Services, and a reserved XML keyword.

Examples

Example 1

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)

Example 2

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)

Example 3

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)

Example 4

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)

Example 5

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)

Example 6

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)

Example 7

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)

Example 8

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)

Example 9

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)

Example 10

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)

Example 11

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.

Example 12

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)

Example 13

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.

Example 14

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)

Example 15

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)

Example 16

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)

Example 17

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.

Example 18

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

Example 19

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)

Example 20

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)

Example 21

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)

Example 22

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)

Usage


xmltable row and column patterns


Generating the rows of the result table




Copyright © 2009. Sybase Inc. All rights reserved. year

View this document as PDF