In my last two articles, “Working with the XML Data Type in SQL Server” and “The XML Methods in SQL Server,” I discussed how to use the XML data type and its methods to store and access XML data. This article takes these topics a step further and explains ways in which you can implement XML within various database objects, including views, functions, computed columns, check constraints, and defaults. If you’re not familiar with how the XML data type and its methods are implemented in SQL Server, you should review the first two articles before starting in on this one. Once you have the basic information, you’ll find that incorporating XML within these other database objects is a relatively straightforward process.
Creating Views
When you create a view, you can include an XML column just like you would a column configured with another data type. For example, the Sales.Store table in the AdventureWorks2008R2 sample database includes the Demographics column, which is configured with the XML type. For each row in the table, the column stores an XML document that contains details about the store described in that row of data.
In the following example, I create a view that retrieves data from the Sales.Store table, including the Demographics column:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
USE AdventureWorks2008R2; IF OBJECT_ID('StoreSurvey') IS NOT NULL DROP VIEW StoreSurvey; GO CREATE VIEW StoreSurvey AS SELECT BusinessEntityID AS StoreID, Demographics AS Survey FROM Sales.Store; GO SELECT Survey FROM StoreSurvey WHERE StoreID = 292; |
There should be no surprises here. I simply specified the name of the XML column as I would other columns and assigned the alias Survey to the column name. The SELECT statement I tagged onto the view definition returns the following results:
1 2 3 4 5 6 7 8 9 10 11 12 |
<StoreSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey" <AnnualSales>800000</AnnualSales> <AnnualRevenue>80000</AnnualRevenue> <BankName>United Security</BankName> <BusinessType>BM</BusinessType> <YearOpened>1996</YearOpened> <Specialty>Mountain</Specialty> <SquareFeet>21000</SquareFeet> <Brands>2</Brands> <Internet>ISDN</Internet> <NumberEmployees>13</NumberEmployees> </StoreSurvey> |
The SELECT statement returns the full XML document, as it’s stored in the Demographics column (for the row with a StoreID value of 292). Although this all fine enough, it’s not particularly noteworthy. Where things get interesting is when you use an XML method within a view to retrieve only part of the XML data. For example, in the following view definition, I use the value() method to retrieve a single value from the XML document in the Demographics column:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
USE AdventureWorks2008R2; IF OBJECT_ID('StoreSales') IS NOT NULL DROP VIEW StoreSales; GO CREATE VIEW StoreSales AS SELECT BusinessEntityID AS StoreID, Demographics.value('declare namespace ns= "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; (/ns:StoreSurvey/ns:AnnualSales)[1]', 'int') AS AnnualSales FROM Sales.Store; GO SELECT AnnualSales FROM StoreSales WHERE StoreID = 292; |
The value() method retrieves the value stored in the <AnnualSales> child element of the <StoreSurvey> element. Because the Demographics column is a typed XML column, the value() method’s first argument includes the namespace reference, along with a reference to the actual element. And the method’s second argument specifies that the value be returned as type int. The SELECT statement I’ve tagged onto the view definition now returns only a single value, 800000.
Because XQuery expressions can get somewhat complex, particularly when they include the namespace reference, views provide a handy way of storing those expressions in your database. For instance, if you want to retrieve annual sales information regularly, a view such as the one above can make the process quite easy.
Creating Functions
As is the case with views, you can also work with XML data within functions. For example, in the following function definition, I return the contents of the Demographics column based on an inputted store ID value (which is equivalent to the table’s BusinessEntityID value):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
USE AdventureWorks2008R2; IF OBJECT_ID('Survey') IS NOT NULL DROP FUNCTION Survey; GO CREATE FUNCTION Survey(@StoreID INT) RETURNS XML AS BEGIN RETURN ( SELECT Demographics FROM Sales.Store WHERE BusinessEntityID = @StoreID ) END; GO SELECT dbo.Survey(292); |
Once again, no surprises here. When I call the function and store ID (292) in a SELECT statement, the function returns the full XML document from the Demographics column for that store. However, notice that I specified XML as the return type. Because XML is being returned, you can even use the XML methods when you call the function. For instance, in the following example, I again call the Survey function, passing in the store ID (292), but this time I also include the value() method:
1 2 3 |
SELECT dbo.Survey(292).value('declare namespace ns= "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; (/ns:StoreSurvey/ns:AnnualSales)[1]', 'int'); |
Notice that I simply add the period and method name when I call the function, followed by the necessary arguments. As you would expect, the function now returns only a single value, in this case, 800000.
I could have just as easily specified the query() method, as in the following example:
1 2 3 |
SELECT dbo.Survey(292).query('declare namespace ns= "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; /ns:StoreSurvey/ns:AnnualSales'); |
Now the SELECT statement returns the entire <AnnualSales> element, along with the namespace information, as shown in the following results:
1 |
<ns:AnnualSales xmlns:ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey">800000</ns:AnnualSales> |
So far, working with XML data within a function has been pretty straightforward, but suppose you don’t want to retrieve the entire XML document. You can instead use XML methods within the function to return specific information from the document. In the following example, I create a function that returns only the value from the <AnnualSales> child element, just like I did earlier in my view definition:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
USE AdventureWorks2008R2; IF OBJECT_ID('AnnualSales') IS NOT NULL DROP FUNCTION AnnualSales; GO CREATE FUNCTION AnnualSales(@survey XML) RETURNS INT AS BEGIN RETURN @survey.value('declare namespace ns= "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; (/ns:StoreSurvey/ns:AnnualSales)[1]', 'int') END; GO SELECT dbo.AnnualSales(Demographics) AS AnnualSales FROM Sales.Store WHERE BusinessEntityID = 292; |
There are a couple things worth noting about this function definition. First, unlike the preceding example, I specified INT as the return type (rather than XML) because INT is the value type returned by the value() method. In addition, I specified XML as the type for the input parameter (@survey). I then used that parameter to call the value() method. That way, I can pass in the column name as the argument to the function, and the value() method will return the specific value from the XML document in the column.
To verify this, I created a SELECT statement that calls the function and passes in the Demographics column as its input argument. Now the function returns a value of 800000, without having to specify an XML method when calling the function itself, as in the preceding example.
Being able to use XQuery expressions within functions provides the same advantages as being able to use them in views. You can save complex expressions to the database that you can call as often as necessary, without having to reconstruct those expressions each time you need them.
Creating Computed Columns
There might be times when you want to use XML data to construct a computed column. One simple way to do that is to convert an entire XML value to a string, as I do in the following example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
USE AdventureWorks2008R2; IF OBJECT_ID('Stores') IS NOT NULL DROP TABLE Stores; GO CREATE TABLE Stores ( StoreID INT PRIMARY KEY, SurveyXml XML, SurveyChar AS CAST(SurveyXml AS NVARCHAR(MAX)) ); GO INSERT INTO Stores(StoreID, SurveyXml) SELECT BusinessEntityID, Demographics FROM Sales.Store; SELECT * FROM Stores WHERE StoreID = 292; |
First, I create the Stores table, which includes one XML column and one calculated column. The calculated column is simply a recasting of the XML column to an NVARCHAR(MAX) column. Notice in the INSERT statement, I retrieve data from the Sales.Store column, including the Demographics column. The Demographics data is converted to NVARCHAR(MAX) when inserted into the SURVEYCHAR column. As you would expect, the SELECT statement returns the full XML document for the SurveyXml column and returns the same document as character data for the SurveyChar column.
This is all well and good if all you want to do is play around with the entire XML document. But suppose instead you want to create a calculated column based on only a portion of that document. That’s where things get a bit sticky.
It turns out that you can’t use XML methods to define a calculated column. However, there is a work-around. You can define a function that retrieves the data you need and then use that function within the column definition, as I do in the following example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
USE AdventureWorks2008R2; IF OBJECT_ID('Stores') IS NOT NULL DROP TABLE Stores; GO IF OBJECT_ID('AnnualSales') IS NOT NULL DROP FUNCTION AnnualSales; GO CREATE FUNCTION AnnualSales(@survey XML) RETURNS INT AS BEGIN RETURN @survey.value('declare namespace ns= "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; (/ns:StoreSurvey/ns:AnnualSales)[1]', 'int') END; GO CREATE TABLE Stores ( StoreID INT PRIMARY KEY, Survey XML, SalesAmount AS dbo.AnnualSales(Survey) ); GO INSERT INTO Stores(StoreID, Survey) SELECT BusinessEntityID, Demographics FROM Sales.Store; SELECT * FROM Stores WHERE StoreID = 292; |
First, I create a function (AnnualSales) that uses the value() method to retrieve the <AnnualSales> value from the inputted XML document, as you saw in a previous example. I then create a table that includes an XML column (Survey) and a calculated column (SalesAmount). Within the SalesAmount column definition, I call the AnnualSales function and pass in the Survey column as its argument. Next, I populate the table with data from the Sales.Store table and then use a SELECT statement to retrieve a row from that table. As to be expected, the Survey column returns the full XML document, and the SalesAmount column returns a value of 800000.
This is, of course, a roundabout way to use XML methods to create a calculated column, but it’s an effective approach nonetheless. The key is making sure that the function targets the XML data that will provide the source for the calculated column. That means any schema and element references must be consistent with the source data.
Creating Check Constraints
Another way you can work with XML in your database is to create check constraints on an XML column. However, chances are, if you decide to do that, you’ll want to use one of the XML methods to create the constraint. The problem with this is that, like calculated columns, you can’t use these methods within the constraint definition. Once again, you must first create a function, as I’ve done in the following example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
USE AdventureWorks2008R2; IF OBJECT_ID('Stores') IS NOT NULL DROP TABLE Stores; GO IF OBJECT_ID('SpecialtyExists') IS NOT NULL DROP FUNCTION SpecialtyExists; GO CREATE FUNCTION SpecialtyExists(@survey XML) RETURNS BIT AS BEGIN RETURN @survey.exist('declare namespace ns= "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; /ns:StoreSurvey/ns:Specialty') END; GO CREATE TABLE Stores ( StoreID INT PRIMARY KEY, Survey XML CHECK(dbo.SpecialtyExists(Survey) = 1) ); GO INSERT INTO Stores(StoreID, Survey) SELECT BusinessEntityID, Demographics FROM Sales.Store; SELECT * FROM Stores WHERE StoreID = 292; |
In this case, the function (SpecialtyExists) uses the exist() method to determine whether the inputted XML document contains the <Specialty> child element within the <StoreSurvey> parent element. The EXIST() method will return 1 if the child element exists within the parent element and will return a 0 if the two elements don’t exist as specified. Consequently, the return type used for the function is BIT. As for the EXIST() method itself, it takes only one argument, the namespace reference and the parent and child elements.
After I defined the function, I created a table (Stores), which includes an XML column (Survey). I’ve also defined a check constraint on the column. The constraint uses the SpecialtyExists function to determine if the data to be inserted into the Survey column contains the specified child and parent elements. If the data contains the elements, the rows are inserted into the table; otherwise the insertion fails.
For instance, when I inserted the data from the Sales.Store table, the Demographics data was added to the store data because that data includes the required child and parent elements. However, if the function had specified a different child element, such as <MainType>, no rows would have been inserted because the check constraint would have evaluated to False in each case.
As you saw with calculated columns, functions provide a workaround for using XML methods to create check constraints on XML columns. In the example above, I used the EXIST() method to verify the existence of an element. However, I could have also used that method to check the existence of an attribute, or I could have used another XML method, although the EXIST() method will probably prove to be the handiest one in this case.
Creating Defaults
If you create a table that includes an XML column, you can create a DEFAULT constraint on that column, as you would other column types. The main difference with the XML column, however, is that if that column is typed XML, your constraint can’t violate the schema associated with that column.
You can create a DEFAULT constraint on an XML column in one of two ways: by implicitly converting the string value to XML or explicitly converting the value. In the following example, I use both approaches:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
USE AdventureWorks2008R2; IF OBJECT_ID('Stores') IS NOT NULL DROP TABLE Stores; GO CREATE TABLE Stores ( StoreID INT PRIMARY KEY, Survey_implicit1 XML DEFAULT '<Survey>Survey to follow</Survey>', Survey_implicit2 XML DEFAULT '<Survey>Survey to follow</Survey>', Survey_explicit1 XML DEFAULT CAST('<Survey>Survey to follow</Survey>' AS XML), Survey_explicit2 XML DEFAULT CAST('<Survey>Survey to follow</Survey>' AS XML) ); GO INSERT INTO Stores (StoreID, Survey_implicit1, Survey_explicit1) SELECT BusinessEntityID, Demographics, Demographics FROM Sales.Store; SELECT * FROM Stores WHERE StoreID = 292; |
Notice that I created four XML columns in the table. I did this to verify how the defaults work when I don’t add data to the columns. The first two XML columns (Survey_implicit1 and Survey_implicit2) each include default definitions that specify an XML value as a string without trying to convert that value to the XML type. For the third and fourth columns (Survey_explicit1 and Survey_explicit2), I specifically cast the columns to the XML type. Because SQL Server automatically converts the string values, you can take either approach.
After I defined the table, I inserted data into the Survey_implicit1 and Survey_explicit1 columns. I then retrieved a row from the table. As to be expected, the Survey_implicit1 and Survey_explicit1 columns returned the entire XML documents and the Survey_implicit2 and Survey_explicit2 columns each returned the value defined in the DEFAULT constraint.
Working with XML
As you saw with the DEFAULT constraints shown in the example above, you can convert non-XML data into XML. You can also convert XML data into other types. In my next article, I’ll provide more specifics on how those conversions work. In the meantime, this article should have provided you with the details you need to work with XML data when creating views, functions, computed columns, check constraints, and defaults. Of course, the XML methods, and the XQuery expressions you can create by using those methods, often play an important role in how you incorporate XML in your database objects, so knowing those methods and the XQuery language can be pivotal in effectively using that XML.
Load comments