Insert into XML Column
Working through Chapter 7 of the Querying Microsoft SQL Server 2012 book for Microsoft’s Exam 70-461, I found the XML examples incomplete for my students. I decided to put together a post on how to create:
- An XML Schema Collection type.
- A table that uses an XML Schema Collection as a column’s data type.
- An example on how you can transfer the contents of a table into the XML Schema Collection.
This post assumes you understand the basics about XML structures. If you’re unfamiliar with XML, please note that everything within it is case sensitive unlike SQL. You raise exceptions when the case of your XML fails to match the case of your XML Schema Collection definitions. I raised the following exception by using a Marvel
element tag in title case when the XML Schema Collection uses a lowercase marvel
element tag:
Msg 6913, Level 16, State 1, Line 2 XML Validation: Declaration not found for element 'Marvel'. Location: /*:Marvel[1] |
The basic marvel
table is defined in this earlier blog post. To ensure you don’t run into conflicts with previously existing objects, you can delete the marvel table with the following syntax:
1 2 | IF OBJECT_ID('studentdb.marvel_xml','U') IS NOT NULL DROP TABLE marvel_xml; |
There is no predefined function that lets you conditionally drop the XML Schema Collection from the data base. The alternative is to query the Microsoft SQL Server data catalog for the existence of a row before dropping the XML Schema Collection, like this:
1 2 3 4 | IF EXISTS (SELECT * FROM sys.xml_schema_collections WHERE name = 'MarvelXMLTable') DROP XML SCHEMA COLLECTION MarvelXMLTable; ELSE SELECT 'Not found.'; |
You can now create the MarvelXMLTable
XML Schema Collection with the following syntax:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | CREATE XML SCHEMA COLLECTION MarvelXMLTable AS N'<?xml version="1.0" encoding="UTF-16"?> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes"> <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" /> <xsd:element name="Marvel"> <xsd:complexType> <xsd:sequence> <xsd:element name="marvel_id" type="sqltypes:int" /> <xsd:element name="avenger_name" type="sqltypes:nvarchar" /> <xsd:element name="first_name" type="sqltypes:nvarchar" /> <xsd:element name="last_name" type="sqltypes:nvarchar" /> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:schema>'; |
After creating the XML Schema Collection, you can confirm whether it works correctly with the following statements. It will fail when you use semicolons on the DECLARE
or SET
lines, so avoid them as shown below:
1 2 3 4 5 6 7 8 | DECLARE @marvel AS XML(MarvelXMLTable) SET @marvel = '<marvel> <marvel_id>1</marvel_id> <avenger_name>Falcon</avenger_name> <first_name>Sam</first_name> <last_name>Wilson</last_name> </marvel>' SELECT @marvel; |
After creating and verifying the integrity of the XML Schema Collection, you can create a marvel_xml
table. The xml_table
column of the marvel_xml
table uses a strongly-typed XML type, as shown:
1 2 3 | CREATE TABLE marvel_xml ( id INT IDENTITY(1,1) CONSTRAINT marvel_xml_pk PRIMARY KEY , xml_table XML(MarvelXMLTable)); |
You can write an INSERT
statement with single element like this:
1 2 3 4 5 6 7 8 9 | INSERT INTO marvel_xml ( xml_table ) VALUES ('<Marvel> <marvel_id>9</marvel_id> <avenger_name>Falcon</avenger_name> <first_name>Sam</first_name> <last_name>Wilson</last_name> </Marvel>'); |
You can insert two elements with an INSERT
statement like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | INSERT INTO marvel_xml ( xml_table ) VALUES ('<Marvel> <marvel_id>9</marvel_id> <avenger_name>Falcon</avenger_name> <first_name>Sam</first_name> <last_name>Wilson</last_name> </Marvel> <Marvel> <marvel_id>10</marvel_id> <avenger_name>Scarlet Witch</avenger_name> <first_name>Wanda</first_name> <last_name>Witch</last_name> </Marvel>'); |
There’s a lot of typing to insert XML literal values. It a lot easier to transfer relational data from a table to an XML type. Unfortunately, Microsoft didn’t make it very easy. The FOR XML AUTO
doesn’t work for two reasons. First, the FOR XML AUTO
clause doesn’t render the correct XML structure. Second, the INSERT
statement disallows nested SELECT
queries that include the FOR XML AUTO
clause.
Here’s the way you insert relational data into an XML type column:
1 2 3 4 5 6 7 8 9 10 11 12 13 | DECLARE @xml_elements NVARCHAR(MAX); SET @xml_elements = N''; WITH x AS (SELECT CONCAT(N'<marvel>' ,N'<marvel_id>',marvel_id,N'</marvel_id>' ,N'<avenger_name>',avenger_name,N'</avenger_name>' ,N'<first_name>',first_name,N'</first_name>' ,N'<last_name>',last_name,N'</last_name>' ,N'</marvel>') AS element FROM marvel) SELECT @xml_elements += element FROM x; INSERT INTO marvel_xml (xml_table) VALUES (@xml_elements); |
Line 1 declares an xml_element
session variable. Line 2 initializes the xml_element
session variable as an empty string. The WITH
clause on lines 3 through 10 creates a Common Table Expression (CTE) with the valid XML structure for all rows in the marvel table. The nested SELECT
statement on line 11 returns data from the CTE and adds returned row to the session-level string variable. Finally, the INSERT
statement on lines 12 and 13 inserts the XML table into the xml_table column.
You can then query the table with the following statement:
SELECT xml_table FROM marvel_xml; |
Click on the return type in the SQL Server Management Studio (SSMS), and the XML structure will expand to show this:
<marvel> <marvel_id>1</marvel_id> <avenger_name>Hulk</avenger_name> <first_name>Bruce</first_name> <last_name>Banner</last_name> </marvel> <marvel> <marvel_id>2</marvel_id> <avenger_name>Iron Man</avenger_name> <first_name>Tony</first_name> <last_name>Stark</last_name> </marvel> <marvel> <marvel_id>3</marvel_id> <avenger_name>Black Widow</avenger_name> <first_name>Natasha</first_name> <last_name>Romanoff</last_name> </marvel> <marvel> <marvel_id>4</marvel_id> <avenger_name>Thor</avenger_name> <first_name>Thor</first_name> <last_name>Odinsson</last_name> </marvel> <marvel> <marvel_id>5</marvel_id> <avenger_name>Captain America</avenger_name> <first_name>Steve</first_name> <last_name>Rogers</last_name> </marvel> <marvel> <marvel_id>6</marvel_id> <avenger_name>Hawkeye</avenger_name> <first_name>Clint</first_name> <last_name>Barton</last_name> </marvel> <marvel> <marvel_id>7</marvel_id> <avenger_name>Winter Soldier</avenger_name> <first_name>Bucky</first_name> <last_name>Barnes</last_name> </marvel> <marvel> <marvel_id>8</marvel_id> <avenger_name>Iron Patriot</avenger_name> <first_name>James</first_name> <last_name>Rhodey</last_name> </marvel> |
As always, I hope this post benefits those who read it.