Querying Over Constructed XML Using Sub-queries

  • [アーティクル]

There are a number of cases where it would be useful to be able to run XQuery statements over the result of an XQuery expression which constructs new values. Unfortunately, directly querying over constructed XML is not supported in SQL Server 2005, so we need to use a work-around. Since the result of a query() method invocation is a new instance of the XML data-type, we can simply assign the results of a query to a variable, then perform further querying over that variable. While this will be acceptable for most cases, it is also possible to do this in a more direct manner, using sub-queries.

To illustrate this, I will use an example from our customer message boards. This particular case involved extracting the individual values from a list-typed simple value, using the nodes() method to create a separate relational row for each entry in the list. To start off with, I will create a simple schema which defines a the value of the foo element to be a string based list type:

 create xml schema collection listExample as '
<xs:schema xmlns:xs="https://www.w3.org/2001/XMLSchema">
<xs:element name="foo">
   <xs:simpleType>
       <xs:list itemType="xs:string" />
  </xs:simpleType>
</xs:element>
</xs:schema>'

We now create an instance of the xml data-type using this schema and some values:

 declare @xml_data xml(listExample)
set @xml_data='<foo>Entry1 Entry2 Entry3</foo>'

The XQuery data() function will extract each individual entry from the list type. Thus, if we run the following query, we get the 3 'entry' items returned:

 select @xml_data.query( 'data(/foo)' )

At this point, we would simply execute the nodes method over this XQuery expression to extract each of the individual values into a separate relational row. For example, using the following query:

 select ref.value('.', 'nvarchar(100)')
from @xml_data.nodes('data(/foo)') T(ref)

Unfortunately, if you try to execute this query, you get the following error message returned:

 Msg 2374, Level 16, State 1, Line 8
XQuery [nodes()]: A node or set of nodes is required for 'nodes()'

Since the data() function causes the creation of new items which are not stored in the base XML data-type instance, we are unable to execute the nodes() method over the result of this expression! In order to run a nodes() method over this, we have to construct a new XML data-type instance in a sub-query and run nodes over the result of that. This can be done as follows:

 select T.ref.value('.', 'nvarchar(100)')
from 
(
      select [Xml]=@xml_data.query('
           for $i in data(/foo) return 
            element temp { $i }
     ')
) A
cross apply A.Xml.nodes('/temp') T(ref)

The sub-query (shown in green) extracts the items out of the list-typed element node foo, returning each individual item as the text node of a newly constructed element named temp. Since the result of the query expression is a new XML data-type instance, we can now run nodes() over this sub-query to get the desired results.

-
Disclaimer:
This posting is provided “AS IS” with no waranties, and confers no rights.
Use of included script samples are subject to the terms specified at https://www.microsoft.com/info/cpyright.htm.

Comments

  • Anonymous
    October 22, 2010
    The comment has been removed