XML

XML#

Querona supports XML data and XML functions natively. You can store XML in tables and variables, produce XML from relational data, and query or shred XML using standard Transact-SQL.

The xml data type#

XML values are held in the xml data type, usable as a column, variable or parameter. XML can be untyped, or typed by associating it with an XML schema collection that validates its content:

CREATE XML SCHEMA COLLECTION dbo.BookSchema AS '<xsd:schema> ... </xsd:schema>';

DECLARE @typed xml(dbo.BookSchema);   -- typed, validated XML
DECLARE @any   xml;                   -- untyped XML

Schema collections are managed with CREATE, ALTER and DROP XML SCHEMA COLLECTION.

Producing XML with FOR XML#

The FOR XML clause turns a relational query result into XML in RAW, AUTO or PATH mode. See SELECT - FOR for the syntax diagram and details.

SELECT BookId, Title
FROM   dbo.Books
FOR XML PATH('book'), ROOT('books');

Prefix the query with WITH XMLNAMESPACES to declare the XML namespace prefixes it uses.

Querying XML#

Query XML with the xml data type methods (the method names are lowercase):

  • .value(xquery, sqltype) — extracts a scalar value and casts it to a SQL type,

  • .query(xquery) — returns an XML fragment,

  • .exist(xquery) — tests whether an XQuery expression matches at least one node (returns bit),

  • .nodes(xquery) — shreds the XML into a rowset, one row per matched node (see below).

SELECT Doc.value('(/book/title)[1]', 'nvarchar(200)') AS Title
FROM   dbo.Books
WHERE  Doc.exist('/book[@year > 2000]') = 1;

Reading SQL values inside XQuery#

An XQuery expression can read a T-SQL variable with sql:variable("@name") and a column of the current row with sql:column("name"). The column name may be qualified with a table name or alias, and delimited with brackets or double quotes:

SELECT b.Id,
       b.Doc.value('(/book/price[@currency = sql:column("b.Currency")])[1]', 'money') AS Price
FROM   dbo.Books b;

A NULL column value is the empty sequence. Columns of type xml, text, ntext, image, sql_variant and CLR types cannot be read this way, and the argument of sql:column must be a string literal. Inside .nodes(), sql:column reads a column of the other side of the CROSS APPLY or OUTER APPLY (or of an enclosing query), never the rowset of the .nodes() call itself:

SELECT b.Id, p.node.value('.', 'money') AS Price
FROM   dbo.Books b
CROSS APPLY b.Doc.nodes('/book/price[@currency = sql:column("b.Currency")]') AS p(node);

Shredding XML into rows#

The .nodes() method turns an XML instance into a rowset with one row per node matched by the XQuery expression. The rowset must be aliased as Table(Column); each row’s column is a reference to the matched node, which you read with the other xml methods:

DECLARE @x xml = '<books><book year="1998">A</book><book year="2005">B</book></books>';

SELECT b.node.value('@year', 'int')      AS Year,
       b.node.value('.', 'nvarchar(50)') AS Title
FROM   @x.nodes('/books/book') AS b(node);

Over an xml table column, combine .nodes() with CROSS APPLY (rows without a match are dropped) or OUTER APPLY (rows without a match are kept with NULLs):

SELECT t.Id, n.node.value('@year', 'int') AS Year
FROM   dbo.Books t
CROSS APPLY t.Doc.nodes('/books/book') AS n(node);

As in SQL Server, the rowset column itself cannot be selected, ordered, grouped or compared — it can only feed the four xml methods, IS [NOT] NULL checks and COUNT(*) — and the XQuery expression must select existing nodes (constructed XML is not supported).

OPENXML remains available as the alternative rowset view of an XML document.

.nodes() over an xml column works on every data source: on SQL Server the query is sent to the source, elsewhere Querona shreds the documents itself.

Current limitations, where Querona evaluates the query itself rather than a SQL Server data source:

  • an xml method inside a correlated subquery, such as WHERE EXISTS (SELECT 1 WHERE n.node.exist('...') = 1), is not supported;

  • in a database on SQL Server, .nodes() under APPLY can fail with “Column … not found” when the other side is a derived table without FROM that SQL Server cannot compute (for example REPLICATE over varchar(max)); read the document from a table or a variable instead.

What is not supported#

For compatibility planning, the following SQL Server XML features are not available in Querona: the .modify() xml method, XML indexes, and FOR XML EXPLICIT (which is accepted for compatibility only and is not executed).

See also