SQL Server XQuery - Selecting a Subset

Question

Take for example the following XML:

Initial Data

<computer_book>
    <title>Selecting XML Nodes the Fun and Easy Way</title>
    <isbn>9999999999999</isbn>
    <pages>500</pages>
    <backing>paperback</backing>
</computer_book>

and:

<cooking_book>
    <title>50 Quick and Easy XML Dishes</title>
    <isbn>5555555555555</isbn>
    <pages>275</pages>
    <backing>paperback</backing>
</cooking_book>

I have something similar in a single xml-typed column of a SQL Server 2008 database. Using SQL Server XQuery, would it be possible to get results such as this:

Resulting Data

<computer_book>
    <title>Selecting XML Nodes the Fun and Easy Way</title>
    <pages>500</pages>
</computer_book>

and:

<cooking_book>
    <title>50 Quick and Easy XML Dishes</title>
    <isbn>5555555555555</isbn>
</cooking_book>

Please note that I am not referring to selecting both examples in one query; rather I am selecting each via its primary key (which is in another column). In each case, I am essentially trying to select the root and an arbitrary subset of children. The roots can be different, as seen above, so I do not believe I can hard-code the root node name into a "for xml" clause.

I have a feeling SQL Server's XQuery capabilities will not allow this, and that is fine if it is the case. If I can accomplish this, however, I would greatly appreciate an example.

Answer 1

Here is the test data I used in the queries below:

declare @T table (XMLCol xml)

insert into @T values 
('<computer_book>
    <title>Selecting XML Nodes the Fun and Easy Way</title>
    <isbn>9999999999999</isbn>
    <pages>500</pages>
    <backing>paperback</backing>
  </computer_book>'), 
('<cooking_book>
    <title>50 Quick and Easy XML Dishes</title>
    <isbn>5555555555555</isbn>
    <pages>275</pages>
    <backing>paperback</backing>
  </cooking_book>')

You can filter the nodes under to root node like this using local-name() and a list of the node names you want:

select XMLCol.query('/*/*[local-name()=("isbn","pages")]')
from @T

Result:

<isbn>9999999999999</isbn><pages>500</pages>
<isbn>5555555555555</isbn><pages>275</pages>

If I understand you correctly the problem with this is that you don't get the root node back.

This query will give you an empty root node:

select cast('<'+XMLCol.value('local-name(/*[1])', 'varchar(100)')+'/>' as xml)
from @T

Result:

<computer_book />
<cooking_book />

From this I have found two solutions for you.

Solution 1

Get the nodes from your table to a table variable and then modify the XML to look like you want.

-- Table variable to hold the node(s) you want
declare @T2 table (RootNode xml, ChildNodes xml)

-- Fetch the xml from your table
insert into @T2
select cast('<'+XMLCol.value('local-name(/*[1])', 'varchar(100)')+'/>' as xml), 
       XMLCol.query('/*/*[local-name()=("isbn","pages")]')
from @T

-- Add the child nodes to the root node
update @T2 set
  RootNode.modify('insert sql:column("ChildNodes") into (/*)[1]')  

-- Fetch the modified XML
select RootNode
from @T2

Result:

RootNode
<computer_book><isbn>9999999999999</isbn><pages>500</pages></computer_book>
<cooking_book><isbn>5555555555555</isbn><pages>275</pages></cooking_book>

The sad part with this solution is that it does not work with SQL Server 2005.

Solution 2

Get the parts, build the XML as a string and cast it back to XML.

select cast('<'+XMLCol.value('local-name(/*[1])', 'varchar(100)')+'>'+ 
            cast(XMLCol.query('/*/*[local-name()=("isbn","pages")]') as varchar(max))+
            '</'+XMLCol.value('local-name(/*[1])', 'varchar(100)')+'>' as xml)
from @T

Result:

<computer_book><isbn>9999999999999</isbn><pages>500</pages></computer_book>
<cooking_book><isbn>5555555555555</isbn><pages>275</pages></cooking_book>

Making the nodes parameterized

In the queries above the nodes you get as child nodes is hard coded in the query. You can use sql:varaible() to do this instead. I have not found a way of making the number of nodes dynamic but you can add as many as you think you need and have null as value for the nodes you don't need.

declare @N1 varchar(10)
declare @N2 varchar(10)
declare @N3 varchar(10)
declare @N4 varchar(10)

set @N1 = 'isbn'
set @N2 = 'pages'
set @N3 = 'backing'
set @N4 = null

select cast('<'+XMLCol.value('local-name(/*[1])', 'varchar(100)')+'>'+ 
            cast(XMLCol.query('/*/*[local-name()=(sql:variable("@N1"),
                                                  sql:variable("@N2"),
                                                  sql:variable("@N3"),
                                                  sql:variable("@N4"))]') as varchar(max))+
            '</'+XMLCol.value('local-name(/*[1])', 'varchar(100)')+'>' as xml)
from @T

Result:

<computer_book><isbn>9999999999999</isbn><pages>500</pages><backing>paperback</backing></computer_book>
<cooking_book><isbn>5555555555555</isbn><pages>275</pages><backing>paperback</backing></cooking_book>