Notes on SQL

Introduction
I tend not to use XML very often, so have limited experience with it. Whilst reading Itzik Ben-Gan’s (b|t) book for 70-761 I came across XMLNAMESPACES for the first time.
The section on this aspect is quite brief, so this blog contains the additional work I used to ensure I understood it.

Background
It is very easy to construct basic XML, using T-SQL. However, XML is not as forgiving when it comes to duplicated column names so requires a way to differentiate elements within an XML document that have the same name.

Test Environment
I created some (very) basic tables for Customer, Order and Product information. A row is inserted into each table, to be used by subsequent queries.

Listing 1: Basic Test Data

CREATE TABLE dbo.Customers
(
    [CustomerID]	INT IDENTITY(1,1) CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED,
    [Name]		NVARCHAR(250),
    [OrderID]	        INT,
    [Address]	        NVARCHAR(250),
    [Updated]	        DATETIME	
);
GO

CREATE TABLE dbo.Orders
(
    [OrderID]			INT IDENTITY(1,1) CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED,
	[Address]		NVARCHAR(250),
	[ProductID]		INT,
	[Qty]			INT,
	[OrderDate]		DATETIME,
	[Updated]		DATETIME
);
GO

CREATE TABLE dbo.Products
(
    [ProductID]			INT IDENTITY(1,1) CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED,
	[ProductCode]		NVARCHAR(50),
	[Name]			NVARCHAR(250),
	[Updated]		DATETIME
);
GO

INSERT INTO dbo.Products (ProductCode ,
		          Name ,
			  Updated
				)
VALUES (   N'AA123' , N'Green Widget' ,	 GETDATE() );

INSERT INTO dbo.Orders (Address ,
			ProductID ,
			Qty ,
			OrderDate ,
			Updated
			   )
VALUES (   N'25 Test Lane, Somewhere' , 1 , 10 , GETDATE() , GETDATE() );

INSERT INTO dbo.Customers (  Name ,
			     OrderID ,
			     Address ,
			     Updated
				)
VALUES (   N'Test Customer' , 1 , N'Destination Lane, Elsewhere' , GETDATE() );
GO

A T-SQL query can have duplicated column names, so the following query has no issues:

Listing 2: Basic SELECT Query with duplicate column names

SELECT c.Name ,
	   c.Address ,
	   o.Address ,
	   o.OrderDate ,
	   o.Updated ,
	   p.ProductCode ,
	   p.Name ,
	   p.Updated
FROM   dbo.Customers AS c
	   INNER JOIN dbo.Orders AS o
		   ON o.OrderID = c.OrderID
	   INNER JOIN dbo.Products AS p
		   ON p.ProductID = o.ProductID;

In the query above, three tables are joined to produce the required results and the columns ‘Address’, ‘Name’ and ‘Updated’ appear on more than one table. Looking at the query it is easy to see (because of the alias’ used) which of these belongs to which table.
However, this can be ambiguous when the code is changed to produce XML output.

One of the simplest ways to produce XML is to specify ‘XML RAW’, which creates an XML fragment (‘fragment’ because there is no ROOT node specified in this example) for each row:

Listing 3: XML RAW

SELECT c.Name ,
	   c.Address ,
	   o.Address ,
	   o.OrderDate ,
	   o.Updated ,
	   p.ProductCode ,
	   p.Name ,
	   p.Updated
FROM   dbo.Customers AS c
	   INNER JOIN dbo.Orders AS o
		   ON o.OrderID = c.OrderID
	   INNER JOIN dbo.Products AS p
		   ON p.ProductID = o.ProductID
FOR XML RAW;

This code will produce the error “Column name ‘Address’ is repeated. The same attribute cannot be generated more than once on the same XML tag.“

Specifying ‘XML AUTO’ will prevent this error, however, the resultant XML fragment is still a little primitive:

Listing 4: XML AUTO

SELECT c.Name ,
	   c.Address ,
	   o.Address ,
	   o.OrderDate ,
	   o.Updated ,
	   p.ProductCode ,
	   p.Name ,
	   p.Updated
FROM   dbo.Customers AS c
	   INNER JOIN dbo.Orders AS o
		   ON o.OrderID = c.OrderID
	   INNER JOIN dbo.Products AS p
		   ON p.ProductID = o.ProductID
FOR XML AUTO;

Listing 5: XML results from listing 4

<c Name="Test Customer" Address="Destination Lane, Elsewhere">
  <o Address="25 Test Lane, Somewhere" OrderDate="2017-12-12T10:20:09.313" Updated="2017-12-12T10:20:09.313">
  </o>
</c>

XML namespace is part of the XML standard and is implemented in SQL by using ‘WITH XMLNAMESPACES’. This can be used to define individual documents or show how different elements within the same XML document are related.

In the following example, ‘p’ is ‘Product’, ‘o’ is ‘Order’ and ‘c’ is ‘Customer’. Notice the format of the aliases – if you use this format without specifying WITH XMLNAMESPACES the query will execute but when you attempt to view the XML (by clicking on the output within SSMS) you will get the message ‘Unable to show XML. The following error happened: ‘c’ is an undeclared prefix‘.

Listing 6: XMLNAMESPACES

WITH XMLNAMESPACES('CustomerData' AS c, 'OrderData' AS o, 'ProductData' AS p)
SELECT
	   [c:Customer].Name ,
	   [c:Customer].Address,
	   [o:Order].Address ,
	   [o:Order].OrderDate ,
	   [o:Order].Updated ,
	   [p:Product].ProductCode ,
	   [p:Product].Name ,
	   [p:Product].Updated
FROM   dbo.Customers AS [c:Customer]
	   INNER JOIN dbo.Orders AS [o:Order]
		   ON [o:Order].OrderID = [c:Customer].OrderID
	   INNER JOIN dbo.Products AS [p:Product]
		   ON [p:Product].ProductID = [o:Order].ProductID
FOR XML AUTO, ELEMENTS, ROOT('CustomerOrders');

In the example above, “CustomerData” is a Uniform Resource Identifier (URI) and the associated prefix is ‘c’. “OrderData” is another URI with a prefix of ‘o’ and “ProductData” is the third URI with an associated prefix of ‘p’. The URI is used to uniquely identify a namespace.

Listing 7: XML results from listing 6

<CustomerOrders xmlns:p="ProductData" xmlns:o="OrderData" xmlns:c="CustomerData">
  <c:Customer>
    <Name>Test Customer</Name>
    <Address>Destination Lane, Elsewhere</Address>
    <o:Order>
      <Address>25 Test Lane, Somewhere</Address>
      <OrderDate>2017-12-12T10:20:09.313</OrderDate>
      <Updated>2017-12-12T10:20:09.313</Updated>
      <p:Product>
        <ProductCode>AA123</ProductCode>
        <Name>Green Widget</Name>
        <Updated>2017-12-12T10:20:09.310</Updated>
      </p:Product>
    </o:Order>
  </c:Customer>
</CustomerOrders>

References
Microsoft Docs – XMLNAMESPACES
XML Master Tutorial
70-761 Querying Data with Transact-SQL