XML fun on Azure SQL

For a client project I was recently asked how to efficiently handle translations in SQL Server. As, in this particular case, the client application was very proficient in processing XML data I chose in cooperation with the development team to use XML as the datatype to handle this specific case. While XML has been suitable as a SQL Server native datatype since SQL 2005, for one reason or the other it’s not often used – with only a very few working examples. This blog post is meant for T-SQL teams that are looking for a working XML example on SQL Server, including schema binding and XML indexes. To make this an interactive playground I’ve chosen to create everything in tempdb. This allows you to really play and experiment with it. Without any further ado, let us start!

A simple clean up

USE tempdb
go
------------------------------------------------------------------
-- Cleanup
------------------------------------------------------------------
BEGIN TRY
       DROP TABLE dbo.dimName
       DROP XML SCHEMA COLLECTION translationsSchemaCollectionSchema
END TRY
BEGIN CATCH
END CATCH
GO

Creating a schema (XSD)
Creating a schema has two mayor advantages and one disadvantage. Let’s walk through them separately.
Advantages:
SQL Server provides native storage of XML data through the xml data type. The XML Schema Collection stores imported XML schemas, which are then used to do the following:

• Validate XML instances
• Type the XML data as it is stored in the database

XML that is stored in a column or variable that a schema is associated with, is referred to as typed XML, because the schema provides the necessary data type information for the instance data. SQL Server uses this information type to optimize data storage. Evenly important, the query processing engine also uses the schema for type checking and optimizing queries and data modification. The biggest reason for this is that, by default, data stored in the typed xml column are stored as XML content, and not as XML documents. This might seem like a trivial detail, but it really makes a difference in the context of a RDBMS. This, however, doesn’t change the fact that you can still use a simple SQL select statement to retrieve the whole dataset as one XML document.

Disadvantage:
The only drawback, that nevertheless isn’t to be understimated, is that you need maintain both the schema as well as the data. This can become complex when you have to take versioning into account, as it might be a lot of work for dynamically changing documents that you just want to store and retrieve as one XML document without filtering. Ok, let’s create the XSD!

GO
------------------------------------------------------------------
-- SCHEMA COLLECTION translationsSchema
------------------------------------------------------------------
USE tempdb
go
CREATE XML SCHEMA COLLECTION TranslationSchema AS
N'<?xml version="1.0" encoding="UTF-16"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="translations">
<xs:complexType>
<xs:sequence>
<xs:element name="language" maxOccurs="unbounded" minOccurs="0">
<xs:complexType>
<xs:sequence>
<xs:element type="xs:string" name="languageCode"/>
<xs:element type="xs:string" name="textValue"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
' ;
GO

The Table
For this blog I’ve created a very simple table object which listens to the glorious name dimName. I also used the previously created XSD.

USE tempdb
go
create table dbo.dimName
(id integer identity(1,1) Not null PRIMARY KEY,
name XML (TranslationSchema) NOT NULL
)
As you can see, I’ve added a clustered primary key. Not only is this a best practice, it is also required when you want to create XML indexes.

Creating XML Indexes
Just like on many other column types, the XML data type allows you to create XML indexes on xml data when the database options are set correctly for working with the xml data type. XML instances are stored in xml type columns as large binary objects (BLOBs). These XML instances can be large, and the stored binary representation of xml data type instances go up to 2GB. Without an index, these binary large objects are shredded at run time to evaluate a query. This shredding can be time-consuming, so in most cases you’ll want to add an index from the moment that you filter your XML data. The default disclaimer is still valid, though: “Index maintenance cost during data modification must always be considered when these columns experience frequent updates and/or inserts”. By design, an XML Index falls into one of two categories and has three subtypes. Let’s walk through them:

The Primary XML Index
The first index on the xml type column must be the primary XML index. This index effectively contains all tags, values, and paths within the XML instances in an XML column. SQL Server then XXXX the clustered primary key to correlate rows in the primary XML index with rows in the table that contains the XML column. The number of index rows is approximately equal to the number of nodes in the XML binary large object. What’s important is that the primary XML index isn’t used when a full XML instance is retrieved.

Creating the Primary XML Index:

CREATE PRIMARY XML INDEX PXML_dbo_dimName
ON dbo.dimName (Name);
GO

The Secondary XML Index
As the primary XML index can become quite large, it can be interesting to create a secondary index on your XML data. You can see this as a filtered index of the XML data containing only one of the 3 types.

• Path: if you need path expressions significantly on XML columns, the PATH secondary XML index is likely to speed up your workload. The exist() method on XML columns in the WHERE clause is most commonly used.
• Value: if your workload involves querying for values within XML instances without knowing the element or attribute names that contain those values, you may want to create the VALUE index. This typically occurs with descendant axes lookups or wildcard queries
• Property: if your workload retrieves multiple values from individual XML instances by using path expressions, clustering paths within each XML instance in the PROPERTY index may be helpful.

In this demo we’ll use both a Value and a Path index.
Creating the Value Secondary Index:

CREATE XML INDEX IXML_dbo_dimName_path
ON dbo.dimName(name)
USING XML INDEX PXML_dbo_dimName FOR VALUE;

Ok now let’s fill this table with some (meaningless) XML data:

INSERT INTO [dbo].[dimName]
([name] )
VALUES
('<translations>
<language><languageCode>NL</languageCode><textValue>Klant</textValue></language>
<language><languageCode>FR</languageCode><textValue>Client</textValue></language>
<language><languageCode>IT</languageCode><textValue>Cliente</textValue></language>
</translations>'),
('<translations>
<language><languageCode>NL</languageCode><textValue>Wagen</textValue></language>
<language><languageCode>FR</languageCode><textValue>Voiture</textValue></language>
<language><languageCode>EN</languageCode><textValue>Car</textValue></language>
</translations>'),
('<translations>
<language><languageCode>NL</languageCode><textValue>Tomaat</textValue></language>
<language><languageCode>FR</languageCode><textValue>Tomate</textValue></language>
<language><languageCode>IT</languageCode><textValue>Pomodoro</textValue></language>
</translations>'),
('<translations>
<language><languageCode>NL</languageCode><textValue>Voetbal</textValue></language>
<language><languageCode>FR</languageCode><textValue>Le Foot</textValue></language>
<language><languageCode>GB</languageCode><textValue>Soccer</textValue></language>
</translations>'),
('<translations>
<language><languageCode>NL</languageCode><textValue>Bier</textValue></language>
<language><languageCode>FR</languageCode><textValue>Biere</textValue></language>
<language><languageCode>SP</languageCode><textValue>Cervessa</textValue></language>
</translations>'),
('<translations>
<language><languageCode>VL</languageCode><textValue>Subiet</textValue></language>
</translations>')
GO

Let the querying begin and  see if we can find all the keys that contain a specific string (using the primary index):

SELECT distinct
d.id, fd.v.value('(.)[1]', 'varchar(10)') as XmlValue
FROM
[dbo].[dimName] d
CROSS APPLY
name.nodes('/translations/language/textValue') AS fd(v)
Where fd.v.value('(.)[1]', 'varchar(10)') in ('Tomaat','Subiet')
GO

Let’s see if we can query the data in another way:

SELECT b.id,
x.XmlCol.value('(languageCode)[1]','NVARCHAR(10)') AS LanguageCode,
x.XmlCol.value('(textValue)[1]','NVARCHAR(100)') AS Vertaling
FROM dbo.dimName b
CROSS APPLY b.name.nodes('/translations/language') x(XmlCol)
where x.XmlCol.value('(languageCode)[1]','NVARCHAR(10)') in ('VL','NL')

Let’s search for a value now:

SELECT b.id,
x.XmlCol.value('(languageCode)[1]','NVARCHAR(10)') AS LanguageCode,
x.XmlCol.value('(textValue)[1]','NVARCHAR(100)') AS Vertaling
FROM dbo.dimName b
CROSS APPLY b.name.nodes('/translations/language') x(XmlCol)
where x.XmlCol.value('(textValue)[1]','NVARCHAR(100)') like ('%ie%')

Now we’ll be using an exist function, so we’ll be swapping to a Path Index:

DROP INDEX IXML_dbo_dimName_path ON dbo.dimName
CREATE XML INDEX IXML_dbo_dimName_path
ON dbo.dimName(name)
USING XML INDEX PXML_dbo_dimName FOR PATH;
GO

Let the query begin:

SELECT *
FROM dbo.dimName
WHERE name.exist('(/translations/language/languageCode[.="GB"])')=1
OR name.exist('(/translations/language/languageCode[.="EN"])')=1
GO

Bringing it all together:

SELECT b.id,
x.XmlCol.value('(languageCode)[1]','NVARCHAR(10)') AS LanguageCode,
x.XmlCol.value('(textValue)[1]','NVARCHAR(100)') AS Vertaling
FROM dbo.dimName b
CROSS APPLY b.name.nodes('/translations/language') x(XmlCol)
where name.exist('(/translations/language/languageCode[.="EN"])') = 1

If you check the query plan, you will now see that these queries use the secondary path index ????. What happens if we use contains and exists together:

SELECT d.id,
x.XmlCol.value('(languageCode)[1]','NVARCHAR(10)') AS LanguageCode,
x.XmlCol.value('(textValue)[1]','NVARCHAR(100)') AS Vertaling
FROM dbo.dimName d
CROSS APPLY d.name.nodes('/translations/language') x(XmlCol)
WHERE
name.exist('(/translations/language/textValue[contains(.,"oo")])')=1

Enjoy!
Karel