Notes on SQL

Introduction

Continuing my series of XML articles I’m going to move from XML RAW to XML AUTO with a handful of examples that share features between the two.

Background

In a previous article I gave example of XML RAW. Although RAW is useful, AUTO takes care of several aspects of XML generation that RAW will not.

Test Environment

The test environment is the same as the previous article.

Listing 1: Create Initial Test Database and Data

CREATE DATABASE XML_Sandpit;
GO

USE XML_Sandpit;
GO

CREATE TABLE dbo.Region(
ID INT NOT NULL,
Area VARCHAR(20) NOT NULL,
Notes VARCHAR(50) NULL
);
GO

INSERT INTO dbo.Region(ID, Area, Notes)
VALUES(1, 'North', 'Northern Area'),
(2, 'South', 'Southern Area'),
(3, 'East', 'Eastern Area'),
(4, 'West', 'Western Area');
GO

CREATE TABLE dbo.Warehouse(
ID INT NOT NULL,
Region INT NOT NULL,
[Name] VARCHAR(50) NOT NULL
);
GO

INSERT INTO dbo.Warehouse(ID, Region, [Name])
VALUES(1, 1, 'North Warehouse 1'),
(2, 1, 'North Warehouse 2'),
(3, 2, 'South Warehouse 1'),
(4, 2, 'South Warehouse 2'),
(5, 3, 'East Warehouse 1'),
(6, 3, 'East Warehouse 2'),
(7, 4, 'West Warehouse 1'),
(8, 4, 'West Warehouse 2');
GO

CREATE TABLE dbo.Products(
ID INT NOT NULL,
--Locations INT NULL,
[Name] VARCHAR(50) NOT NULL,
[Description] VARCHAR(100) NULL
);
GO

INSERT INTO dbo.Products(ID, [Name], [Description])
VALUES (1, 'Grommet 1A', 'Steel 5mm'),
(2, 'Widget 12', 'A new gadget'),
(3, 'Oddment 6', 'A thing');
GO

CREATE TABLE dbo.ProductWarehouse(
ID INT NOT NULL,
ProductID INT NOT NULL,
WarehouseID INT NOT NULL
);
GO

INSERT INTO dbo.ProductWarehouse(Id, ProductID, WarehouseID)
VALUES (1, 1, 1),
(2, 1, 2),
(3, 1, 3),
(4, 1, 4),
(5, 2, 1),
(6, 2, 2),
(7, 3, 3),
(8, 3, 4);
GO

Querying the Data

A simple SELECT statement shows various details about warehouse locations and products.

Listing 2: Basic Query

    SELECT Region.Area,
	   Warehouse.[Name],
	   Products.[Name],
	   Products.[Description]
   FROM dbo.Region Region
   LEFT OUTER JOIN dbo.Warehouse Warehouse ON Warehouse.Region = Region.ID
   LEFT OUTER JOIN dbo.ProductWarehouse pw ON pw.Warehouseid = Warehouse.ID
   INNER JOIN dbo.Products Products ON Products.ID = pw.ProductID
   ORDER BY Region.Area,
	    Warehouse.[Name],
	    Products.[Name];

Figure 1: Result from Listing 2

XML AUTO

Using a query taken from the previous article in this series, the XML RAW is replaced with XML AUTO.

Listing 3: Query with XML AUTO

SELECT Region.Area,
	   Warehouse.[Name],
	   Products.[Name],
	   Products.[Description]
FROM dbo.Region Region 
LEFT OUTER JOIN dbo.Warehouse Warehouse ON Warehouse.Region = Region.ID
LEFT OUTER JOIN dbo.ProductWarehouse pw ON pw.Warehouseid = Warehouse.ID
INNER JOIN dbo.Products Products ON Products.ID = pw.ProductID
ORDER BY Region.Area,
         Warehouse.[Name],
         Products.[Name]
FOR XML AUTO;

Figure 2: Result from Listing 3

Click on this XML row and another tab will be opened, showing the detail within this row:

Figure 3: Clicking on the XML shows this

The default output from AUTO is substantially different from the output of RAW (as shown in the previous article).

There are several obvious changes to the output:

1. This version has catered for the attribute of ‘Name’ within two of the tables, because…
2. The table or alias names have been incorporated into the document, as the element name.
3. The XML has been structured by the order that the tables are initially listed in the SELECT clause. So each level depends upon the initial sequence that the table rows have been listed in the SELECT.

To demonstrate point 3 there are a couple of simple examples.

Firstly, adding another column at the foot of the SELECT statement does not change the ‘shape’ of the XML. 

Listing 4: Example 1 – Add another column from the Region table

SELECT Region.Area,
	   Warehouse.[Name],
	   Products.[Name],
	   Products.[Description],
	   Region.Notes
FROM dbo.Region Region 
LEFT OUTER JOIN dbo.Warehouse Warehouse ON Warehouse.Region = Region.ID
LEFT OUTER JOIN dbo.ProductWarehouse pw ON pw.Warehouseid = Warehouse.ID
INNER JOIN dbo.Products Products ON Products.ID = pw.ProductID
ORDER BY Region.Area,
         Warehouse.[Name],
         Products.[Name]
FOR XML AUTO;

Figure 4: Result from Listing 4

The ‘Notes’ data has not been listed as a separate element, because a column from the same table had been specified earlier in the SELECT – it has become an attribute within the ‘Region’ element.

We can change the ‘shape’ of the XML by changing the order of the columns in the SELECT statement.

Listing 5: Example 2 – Change the order of the SELECT

SELECT Warehouse.[Name], 
	   Products.[Name], 
	   Products.[Description], 
	   Region.Notes ,
	   Region.Area
FROM dbo.Region Region 
LEFT OUTER JOIN dbo.Warehouse Warehouse ON Warehouse.Region = Region.ID 
LEFT OUTER JOIN dbo.ProductWarehouse pw ON pw.Warehouseid = Warehouse.ID 
INNER JOIN dbo.Products Products ON Products.ID = pw.ProductID 
ORDER BY Region.Area, 
		 Warehouse.[Name], 
		 Products.[Name] 
FOR XML AUTO;

Figure 5: Result from Listing 5

‘Warehouse’ is now the top-level element, followed by Products and Region in hierarchical format – due to the sequence of the columns in the SELECT.

 

Naming the Row Elements

In the example scripts I have given the tables aliases that at first glance might seem odd – they are the same as the table names. This is because the element names are taken from the table or alias names, so if no alias is supplied then the schema name will be included too.

Listing 6: No alias on the tables

SELECT Warehouse.[Name], 
	   Products.[Name], 
	   Products.[Description], 
	   Region.Notes ,
	   Region.Area
FROM dbo.Region
LEFT OUTER JOIN dbo.Warehouse ON Warehouse.Region = Region.ID 
LEFT OUTER JOIN dbo.ProductWarehouse pw ON pw.Warehouseid = Warehouse.ID 
INNER JOIN dbo.Products ON Products.ID = pw.ProductID 
ORDER BY Region.Area, 
		 Warehouse.[Name], 
		 Products.[Name] 
FOR XML AUTO;

Figure 6: Result from Listing 6

ELEMENTS

XML AUTO removes the issue that RAW had, with duplicate column names but it still lists different fields for the same element as attributes. To have each property detailed within their own xml tags, you can use ELEMENTS – just like the XML RAW demonstrated.

Listing 7: Specifying ELEMENTS

SELECT Region.Area, 
	   Warehouse.[Name], 
	   Products.[Name], 
	   Products.[Description], 
	   Region.Notes 
FROM dbo.Region Region 
LEFT OUTER JOIN dbo.Warehouse Warehouse ON Warehouse.Region = Region.ID 
LEFT OUTER JOIN dbo.ProductWarehouse pw ON pw.Warehouseid = Warehouse.ID 
INNER JOIN dbo.Products Products ON Products.ID = pw.ProductID 
ORDER BY Region.Area, 
		 Warehouse.[Name], 
		 Products.[Name] 
FOR XML AUTO, ELEMENTS;

Figure 7: Result from Listing 7 (truncated for brevity)

Using ROOT

In all of the previous figures, there is a red underline for the second element. The message ‘XML document cannot contain multiple root level elements’, as with the RAW article in my previous article. The solution is the same – specify ROOT, with an optional name.

Listing 8: Specifying ROOT

SELECT Region.Area, 
	   Warehouse.[Name], 
	   Products.[Name], 
	   Products.[Description], 
	   Region.Notes 
FROM dbo.Region Region 
LEFT OUTER JOIN dbo.Warehouse Warehouse ON Warehouse.Region = Region.ID 
LEFT OUTER JOIN dbo.ProductWarehouse pw ON pw.Warehouseid = Warehouse.ID 
INNER JOIN dbo.Products Products ON Products.ID = pw.ProductID 
ORDER BY Region.Area, 
		 Warehouse.[Name], 
		 Products.[Name] 
FOR XML AUTO, ROOT('Warehouses');

Figure 8: Result from Listing 8

 

Conclusion

AUTO takes some of the complication out of creating well-formatted XML data and the same options can be added as seen with XML RAW.

References

XML Data

FOR XML
XML and JSON Recipes for SQL Server – Amazon.com