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).

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

Shredding XML into rows#

Use OPENXML to transform an XML document into a relational rowset that can be queried, joined and filtered like any other table — useful for importing XML or reporting over it with standard SQL.

What is not supported#

For compatibility planning, the following SQL Server XML features are not available in Querona: the .nodes() and .modify() xml methods, XML indexes, and FOR XML EXPLICIT (which is accepted for compatibility only and is not executed). To shred XML into rows, use OPENXML in place of .nodes().

See also