The new WITH XMLNAMESPACES clause
FOR XML in SQL Server 2000 puts the burden of generating and maintaining XML namespaces completely on the query writer. XML namespace declaration attributes have to be created like every other attribute with the namespace URI being the column value. Unless the generated XML was in attribute-centric form, this meant that the query has to be written using the EXPLICIT mode. For example, the following query puts the resulting Customer elements and its property elements into the namespace urn:example.com/customer:
SELECT 1 as tag, NULL as parent,
'urn:example.com/customer' as "cust:Customer!1!xmlns:cust",
CustomerID as "cust:Customer!1!cust:CustomerID!element",
ContactName as "cust:Customer!1!cust:ContactName!element"
FROM Customers
FOR XML EXPLICIT
The result of the query is (only first two elements shown)
<cust:Customer xmlns:cust="urn:example.com/customer">
<cust:CustomerID>ALFKI</cust:CustomerID>
<cust:ContactName>Maria Anders</cust:ContactName>
</cust:Customer>
<cust:Customer xmlns:cust="urn:example.com/customer">
<cust:CustomerID>ANATR</cust:CustomerID>
<cust:ContactName>Ana Trujillo</cust:ContactName>
</cust:Customer>
Since namespace declarations are not really attributes in the XML data model, the PATH mode does not allow them to be specified as attributes (you can also not query them as attributes in XPath).
In order to simplify the use of XML namespaces in FOR XML, we have added support for the WITH XMLNAMESPACES clause starting in SQL Server 2005's April CTP. The WITH XMLNAMESPACES clause that has been defined in the SQL:2003 standard is an extension to the WITH clause that is commonly used to define common table expressions and can be placed on top-level SQL statements such as SELECT, INSERT and UPDATE statements and can be used inside a CREATE VIEW statement. It can be used with the RAW, AUTO and PATH mode but not with the XMLSCHEMA and XMLDATA directives, or the EXPLICIT mode. The old way of creating namespaces is still supported for the SQL Server 2000 modes but cannot be mixed with the WITH XMLNAMESPACES clause. To disambiguate the WITH clause from the WITH expression to associate hints, T-SQL statements that preceed WITH clauses need to be terminated with a semi-colon (;). The following query places the customer and order data into different namespaces and adds a root node in a default namespace.
WITH XMLNAMESPACES (
DEFAULT 'urn:example.com/doc'
, 'urn:example.com/customer' as "c"
, 'urn:example.com/order' as "o"
)
SELECT CustomerID as "@ID",
(SELECT OrderID as "@OrderID"
from Orders
where Customers.CustomerID=Orders.CustomerID
FOR XML PATH('o:Order'), TYPE
) as "c:Orders",
CompanyName as "c:CompanyName",
ContactTitle as "c:ContactName/@ContactTitle",
ContactName as "c:ContactName/text()",
PostalCode as "c:Address/@ZIP",
Address as "c:Address/c:Street",
City as "c:Address/c:City"
FROM Customers
FOR XML PATH('c:Customer'), ROOT('doc')
As the following partial result shows, the XML namespace declarations are currently added at the top-level elements of every FOR XML selection:
<doc xmlns:o="urn:example.com/order" xmlns:c="urn:example.com/customer" xmlns="urn:example.com/doc">
<c:Customer ID="ALFKI">
<c:Orders>
<o:Order xmlns:o="urn:example.com/order" xmlns:c="urn:example.com/customer" xmlns="urn:example.com/doc" OrderID="10643" />
<o:Order xmlns:o="urn:example.com/order" xmlns:c="urn:example.com/customer" xmlns="urn:example.com/doc" OrderID="10692" />
<o:Order xmlns:o="urn:example.com/order" xmlns:c="urn:example.com/customer" xmlns="urn:example.com/doc" OrderID="10702" />
<o:Order xmlns:o="urn:example.com/order" xmlns:c="urn:example.com/customer" xmlns="urn:example.com/doc" OrderID="10835" />
<o:Order xmlns:o="urn:example.com/order" xmlns:c="urn:example.com/customer" xmlns="urn:example.com/doc" OrderID="10952" />
<o:Order xmlns:o="urn:example.com/order" xmlns:c="urn:example.com/customer" xmlns="urn:example.com/doc" OrderID="11011" />
</c:Orders>
<c:CompanyName>Alfreds Futterkiste</c:CompanyName>
<c:ContactName ContactTitle="Sales Representative">Maria Anders</c:ContactName>
<c:Address ZIP="12209">
<c:Street>Obere Str. 57</c:Street>
<c:City>Berlin</c:City>
</c:Address>
</c:Customer>
...
The above query used the DEFAULT clause for adding a default namespace. Note that if there are nested XML documents with no default namespace included in the result, a slight performance penalty has to be paid to make sure that they preserve their absence of a default namespace.
Finally, the WITH XMLNAMESPACES clause can also be used to provide namespace bindings for the XQuery and XML DML methods on the XML data type. This can especially be useful if several methods are invoked in the same SQL expression. For example, we can now write the following expression from the TechEd 2004 demo:
WITH XMLNAMESPACES ('urn:example/customer' as "c")
SELECT N1.customer.query('.') as customer,
N1.customer.value('c:name[1]', 'nvarchar(20)') as CustomerName,
N2."order".value('@id', 'int') as OrderID,
N1.customer.value('../@id', 'nvarchar(5)') as DocID
FROM XMLdoc
cross apply XMLdoc.doc.nodes( '/c:doc/c:customer') as N1(customer)
cross apply N1.customer.nodes( 'c:order') as N2("order")
[Most of this weblog posting will at some point be added to the FOR XML whitepaper on MSDN]