Mike Amundsen’s WP Space

on my last INETA trip (to Plano, TX) i was asked a question about generating XML from SQL2005 that included support for XML namespaces. Unfortunately, I didn’t have my act together at that moment and was not able to show off this cool feature of SQL2005. But it is actually very easy to generate namespace-enabled XML using a new feature of SQL2005 – the NAMESPACES keyword

The NAMESPACES Keyword

There are a number of powerful new keywords in SQL2005 that make it easier to support XML. One of them is NAMESPACES. AS you would expect, this keyword is used to output XML from SQL Server that includes the proper namespace designations.

For example, let’s assume you want to create the following output from the AUTHORS data table from the PUBS database:

<rdf:RDF xmlns:xmlp="http://www.amundsen.com/rdf/xmlp/1.0/" xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#">
  <rdf:Description>
    <xmlp:id>123-45-6789</xmlp:id>
    <xmlp:firstname>mike</xmlp:firstname>
    <xmlp:lastname>amundsen</xmlp:lastname>
    <xmlp:phone>123-456-7890</xmlp:phone>
    <xmlp:address>123 main st</xmlp:address>
    <xmlp:city>byteville</xmlp:city>
    <xmlp:state>md</xmlp:state>
    <xmlp:zip>94609</xmlp:zip>
  </rdf:Description>
</rdf:RDF>

Creating the query that results in the above output is actually pretty straight forward. I’ll go through the steps below and toss in a few other nice features of SQL2005 along the way.

Step-By-Step XML Namespace Output from SQL2005

All you need to do is to build your query as you normally would. First, the simple SELECT statement to get the data you need:

select * from pubs.dbo.authors

Next you need to tell SQL Server to return an XML version of the data:

select * from pubs.dbo.authors
for xml raw, elements

The above query is nice, but is lacking a very important item – the root element. This was a classic problem with SQL2000 – so much so that when Microsoft released the SqlXml assembly for .NET, they included a workaround method that allowed programmers to set the root on the client site. However, the release of SQL2005 gave the SQL team a chance to fix this omission. Now, all you need to do is add the ROOT keyword to your query like this:

select * from pubs.dbo.authors
for xml raw, elements, root('RDF')

One more thing. With the above format, each collection of fields is enclosed in an element called "row." Not too creative, and not what we need. You can control the enclosing element name for each row by decorating the ROW keyword with a string name like this:

select * from pubs.dbo.authors
for xml raw('Description'), elements, root('RDF')

So far, so good. We have solid (valid) XML output, but no namespaces yet. here’s the secret sauce built into SQL2005. you preface the query with a list of namespaces to include at part of the root element. It works like this:

with xmlnamespaces 
(
'http://www.w3.org/1999/02/22-rdf-syntax-ns#' as rdf,
'http://www.amundsen.com/rdf/xmlp/1.0/' as xmlp
)
select * from pubs.dbo.authors
for xml raw('Description'), elements, root('RDF')

Now the output includes xmlns elements in the root tag. That’s good – we have namespaces now! However, we also need to decorate the various elements in the output with the proper namespace prefixes. That works like this:

with xmlnamespaces 
(
    'http://www.w3.org/1999/02/22-rdf-syntax-ns#' as rdf,
    'http://www.amundsen.com/rdf/xmlp/1.0/' as xmlp
)
select
    au_id as 'xmlp:id', 
    au_fname as 'xmlp:firstname',
    au_lname as 'xmlp:lastname',
    phone as 'xmlp:phone',
    address as 'xmlp:address',
    city as 'xmlp:city',
    state as 'xmlp:state',
    zip as 'xmlp:zip'
    from pubs.dbo.authors
for xml raw('rdf:Description'), elements, root('rdf:RDF')

Note that I also cleaned up the element names and added namespace designations
to the row and root elements. Now the final output will include XML namespace
designations for each element as needed.

Technorati Tags

I tag my posts for easy indexing at Technorati.com

• xml
• sqlserver
• microsoft
• ineta