I haven’t been able to do much XQuery development recently which is a shame as I love developing with the technology in SQL. In the past I used to work with xslt’s a lot and they immensely powerful, but since moving to xquerying in SQL, it is now my first choice for quick xml transformations. Especially if the XML is already stored within the database and not externally.
Lately though I have been involved in a data migration which also involved the transformation of a number of xml structures to new schemas so this has allowed me to delve into this world once again, albeit unfortunately for only a brief while.
One particular type of transformation that I had to do during this project was whilst within a particular xml structure, I had to move a number of xml node elements from where they are stored in the xml to their parent level and remove the original parent node, which would now be empty of course.
Imagine this xml structure which provides some detail regarding a book:
<Book>
<BookFormats>
<NumberOfBookFormats>2</NumberOfBookFormats>
<Format Type="Kindle">
<Price>5.00</Price>
<Currency>£</Currency>
<AvailableDate>2010-05-01</AvailableDate>
</Format>
<Format Type="Paperback">
<Price>8.00</Price>
<Currency>£</Currency>
<AvailableDate>2010-03-20</AvailableDate>
</Format>
</BookFormats>
</Book>
Basically, I want to move all the child nodes from <BookFormats> to the same level as <BookFormats> and then remove the <BookFormats> node completely from the xml. To put it simply, I just want to remove the <BookFormats> node but keep the child elements there.
Now I hang my head in shame in knowing that many many (many!!) years ago I may have approached this by quickly hacking the xml by using some string manipulation and replacing the opening and closing “BookFormats” nodes with nothing e.g.
SELECT CAST(REPLACE(REPLACE(
CAST(@XML AS NVARCHAR(MAX)), '<BookFormats>', '')
, '</BookFormats>', '') AS XML)
(Although I’m sure I’m not alone here in having once used this very suspect approach!!!)
But in fact, moving all child nodes to the same level as its parent is really simple to do, much easier on the eye, and obviously a lot safer using XQuery. For example, the above task is achieved by this tiny piece of code (download script at bottom):
SELECT @XML.query('for $x in (/Book)
return
<Book>
{$x/BookFormats/*}
</Book>
')
This simple FLOWR statement returns an xml structure containing a top level <Book> node, followed by all child nodes under the <BookFormats> node. This eliminates the <BookFormats> node entirely. Running this query returns:
<Book>
<NumberOfBookFormats>2</NumberOfBookFormats>
<Format Type="Kindle">
<Price>5.00</Price>
<Currency>£</Currency>
<AvailableDate>2010-05-01</AvailableDate>
</Format>
<Format Type="Paperback">
<Price>8.00</Price>
<Currency>£</Currency>
<AvailableDate>2010-03-20</AvailableDate>
</Format>
</Book>
Say for example though, you wanted to return all child nodes except for certain named nodes. Again this is easy to do as well by using the function local-name(). This next example follows on from the previous one, but it will test the name of each immediate child node under <BookFormats> and exclude any nodes that are named “NumberOfBookFormats”.
SELECT @XML.query('for $x in (/Book)
return
<Book>
{$x/BookFormats/*[local-name(.) != "NumberOfBookFormats"]}
</Book>
')
This query is the same as before, but it now has a predicate which tests the name of each child node to ensure that it does not equal “NumberOfBookFormats”. Running this returns:
<Book>
<Format Type="Kindle">
<Price>5.00</Price>
<Currency>£</Currency>
<AvailableDate>2010-05-01</AvailableDate>
</Format>
<Format Type="Paperback">
<Price>8.00</Price>
<Currency>£</Currency>
<AvailableDate>2010-03-20</AvailableDate>
</Format>
</Book>
Enjoy!
