Notes on SQL

Introduction

Most systems that I have worked with make use of the XML data type. I don’t find it to be the most intuitive data type to work with but most aspects of it can be built upon from a few basic examples. This will be part of a developing series that will cover XML and JSON.

Background

The eXtensible Markup Language (XML) is a way of defining data and the relationship with other data in one ‘document’. The XML data type was introduced into SQL Server with version 2005. Prior to that, the VARCHAR or TEXT data types were used for storing such data and it wasn’t a friendly process.

With the advent of the XML data type a slew of properties and functions were introduced and developed over the following years, to help with the generation and manipulation of such data.

One of the more basic ways of generating XML from SQL Server data is by using XML RAW.

Test Environment

Create a simple test database with data.

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 RAW

The most basic XML formatting is XML RAW. The output from a query is converted to one XML field, where each row of data returned is with a <row> element.

Listing 3: Query with XML RAW

    SELECT Region.Area,
	   Warehouse.[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 RAW;

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

If the element name of ‘row’ isn’t suitable, another name can be specified.

Listing 4: Change the name of <row>

SELECT Region.Area,
       Warehouse.[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 RAW('Warehouse');

Figure 4: Result from Listing 4

Duplicate Column Names in XML RAW
Notice that in Listing 2 there were two columns named ‘Name’, whereas in listing 3 and 4 I only specified the one. This is because XML RAW, in its most basic form, cannot have duplicated names within each <row> element.

In the following listing, the row ‘Name’ is selected from both the Warehouse and Products tables.

Listing 5: SELECT XML RAW with duplicate column names

    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 RAW;

The result of this query is:
Column name ‘Name’ is repeated. The same attribute cannot be generated more than once on the same XML tag.

This is because the XML RAW in this example is attempting to show two attributes called ‘Name’ within one element – ‘<row>’. Duplicate attributes are not allowed within the same element.

To get around this, specifying ELEMENTS will output each column as a separate element within <row>, which will allow such a result.

Listing 6: SELECT XML RAW with ELEMENTS specified

    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 RAW, ELEMENTS

Figure 5: Result from Listing 6

 ROOT

In Figure 4, notice that the second ‘<row>’ element has a red underline, signifying an error of some type. If you move the mouse to this field, a help-text will show more information.

Figure 6: Error Message

The message ‘XML document cannot contain multiple root level elements’ means that there should be one unique XML ‘tag’ that surrounds the entire document.

To create this XML with one root-level element, the option ROOT can be used.

Listing 7: Specifying ROOT

    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 RAW, ELEMENTS, ROOT

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

The default name of ‘root’ can be replaced with something more appropriate.

Listing 8: Specifying root name

    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 RAW, ELEMENTS, ROOT('Warehouses');

Figure 8: Result from query 8 (truncated for visibility)

Conclusion

Although referred to as the most basic of XML options, RAW still has a variety of possible configurations. As this series develops, several of the aspects shown in this article will appear again.

References

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