How do I get (N) rows from a SQL Server XQuery?

Every time I demonstrate using XQuery against a table that contains an SQL Server XML data type to a new class, a get about one out of four people with puzzled looks. And they're the ones who know XPath and XSLT. The part that seems weird to them is that a XQuery against what they see as "a collection of documents in a table" produces a one-row answer for each row in the table. You can tell they were expecting a single document (or fragment) answer. So,

SELECT xmlcol.query('/somequery') FROM xmltable

produces a rowset with a single column; there is one row in the "answer" for each row in the table. Eventually someone pipes up with: "well, I want just one answer". Can't you do that?

You can actually get just one answer in a few different ways. The easiest is to query the entire table using SELECT…FOR XML and do the query on the result:

SET @x = (SELECT xmlcol from xmltable FOR XML RAW, TYPE)
SELECT @x.query('/somequery')

The only problem you can run into with this is the maximum size of an SQL Server XML data type instance, which is ~2gig. You also have to take the "extra" elements that FOR XML generates into account in your final query. You can also do the original XQuery, then do a FOR XML to combine the results together.

But, you can even cut it finer than that. Using an XML.nodes query with CROSS APPLY or OUTER APPLY, you can generate one row in the answer for each node in the sequence that XML.nodes returns (including or ignoring empty-sequence rows, that's what CROSS vs OUTER apply does). These "intermediate rows" from XML.nodes actually contain "virtual documents" with the context node (starting point of the XQuery) possibly pointing somewhere other than the root. You then do XQuery against the virtual document on the left-hand side of the CROSS APPLY. Like this:

SELECT custid, tab.col.query('itemno') –get itemno subelement of lineitem
  FROM xmlinvoices
    xmlinv.nodes('//lineitem')) as tab(col)

In this case, if you have a 10-row table, but the 10 XML documents in the table contain 42 lineitem elements, you get 42 rows in the answer. Cool 'eh? If you currently use OPENXML, learn XML.nodes, because this will eventually replace OPENXML in our toolbox. It's MUCH less memory-intensive than OPENXML.

Finally (someone asked this yesterday), if you DO want to start with 1-row/answer per row in table and just throw away the rows that contain empty sequence answer, a simple subquery will do:

  (SELECT xmlcol.query('/somequery') AS col FROM xmltable) AS x
  WHERE col <> ''

There. Exactly the answer you want…whatever the problem. Have fun.

3 thoughts on “How do I get (N) rows from a SQL Server XQuery?

  1. hi Bob,

    given these situations where you’re generating XML via an XQuery, how do you typically grab that data via ADO.NET?

    use ExecuteXmlReader? use ExecuteReader and then call XmlDocument.LoadXml on the string valued rows?


  2. Hi Kirk,

    You use ExecuteReader and can have each row that contains a column of type XML. The XML data type is returned via ADO.NET as either a String (which can be loaded into an XmlReader) or as type System.Data.SqlTypes.SqlXml. SqlXml.CreateReader creates an XmlReader over the XML that this column contains.

    I wrote an article on this subject for MSDN online. Look for: XML Data Type support in ADO.NET 2.0: Handling XML from SQL Server 2005.

  3. Instead of writing:

    SET @x = (SELECT xmlcol from xmltable FOR XML RAW, TYPE)
    SELECT @x.query(‘/somequery’)

    I think it would be better to write:

    SELECT xmlcol.query(‘/somequery’) from xmltable FOR XML PATH(”), TYPE

    The main difference between the two is that in the first case, you can easily correlate information from different rows, which you cannot in the second. If you find many people needing the correlation ability, please let me know.

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.