We help IT Professionals succeed at work.

SQL Server XQuery - Selecting a Subset

kaufmed
kaufmed used Ask the Experts™
on
Take for example the following XML:

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

Open in new window


In SQL Server XQuery (I'm using 2008, but 2005 example *should* suffice), I would like to select the root node and a subset of children. Let's say:

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

Open in new window


Can this be done? I realize SQL Server supports only a subset of the XQuery specification; a FLOWR or non-FLOWR solution is fine. I am willing to accept any working answer, but if the process seems to be lengthy or involved, then I'll probably rework my logic.

Thanks in advance!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2011

Commented:
Hi kaurmed,

Please try the following code:

declare @docIn xml
declare @docOut xml

set @docIn = 
'<book>
    <title>Selecting XML Nodes the Fun and Easy Way</title>
    <isbn>9999999999999</isbn>
    <pages>500</pages>
    <backing>paperback</backing>
</book>'

set @docOut = (select @docIn.query('/book/title'), @docIn.query('/book/pages') for xml raw('book'))

select @docOut

Open in new window


I hope this helps.
kaufmedGlanced up at my screen and thought I had coded the Matrix...  Turns out, I just fell asleep on the keyboard.
Most Valuable Expert 2011
Top Expert 2015

Author

Commented:
Hello wdosanjos,

This is my fault as I didn't specify, but I won't be able to hard-code "book" as you have in "raw('book')" because my XML column can have different roots between rows.

Also, what is the performance implication of having multiple "query()" calls in a single query? This example is trivial, but I can have multiple elements returned per query. My current query is attempting to retrieve 10 children.
kaufmedGlanced up at my screen and thought I had coded the Matrix...  Turns out, I just fell asleep on the keyboard.
Most Valuable Expert 2011
Top Expert 2015

Author

Commented:
P.S.

The ultimate goal is to use XML serialization to deserialize the object back into code. I am attempting to return only the children that I need for processing rather than the whole document. It may come down to deserializing the whole document, though.
Top Expert 2011

Commented:
Can your provide a sample of the scenario you described and the expected result?

BTW, the sp_xml_preparedocument system proc can be used to reduce the parsing impact by caching the internal parsed tree.  But its practically depends on how you are retrieving and consuming the xml document.
kaufmedGlanced up at my screen and thought I had coded the Matrix...  Turns out, I just fell asleep on the keyboard.
Most Valuable Expert 2011
Top Expert 2015

Author

Commented:
Modifying the original XML, let's say I have the following two documents in my XML column:

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

Open in new window


and

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

Open in new window


Both of these would exist in the XML column--same database and table. I want to query so that, depending on which row I am interested in (via its primary key--a separate column) I can return, for example:

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

Open in new window


or

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

Open in new window


So children can vary, and I'm building that part of the query in code. I can't hard-code the root because as you can see the root can be different. I could query for the root node's name ahead of time, but I was hoping to only have to send one query to the DB.
Top Expert 2011
Commented:
Please check the following approach.

declare @MyTable table (id int identity primary key, xmldoc xml)

insert into @MyTable (xmldoc) values('<computer_book>
    <title>Selecting XML Nodes the Fun and Easy Way</title>
    <isbn>9999999999999</isbn>
    <pages>500</pages>
    <backing>paperback</backing>
</computer_book>')

insert into @MyTable (xmldoc) values('<cooking_book>
    <title>50 Quick and Easy XML Dishes</title>
    <isbn>5555555555555</isbn>
    <pages>275</pages>
    <backing>paperback</backing>
</cooking_book>')

select id, '<'+r+'>'+cast(xmldoc.query('(//title,//pages)') as varchar(4000))+'</'+r+'>'
from (select id, xmldoc, xmldoc.value('local-name(/*[1])','varchar(20)') r from @MyTable) t

Open in new window

kaufmedGlanced up at my screen and thought I had coded the Matrix...  Turns out, I just fell asleep on the keyboard.
Most Valuable Expert 2011
Top Expert 2015

Author

Commented:
wdosanjos,

I appreciate the help. The approach is valid, but I was hoping to avoid a concatenation-based approach. I have reworked my application logic, so this approach is no longer desired. However, I acknowledge that your approach is a correct in solving the original question.

Thanks!