{"id":1262,"date":"2012-01-04T00:00:00","date_gmt":"2012-01-04T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/incorporating-xml-into-your-database-objects\/"},"modified":"2021-08-16T15:02:02","modified_gmt":"2021-08-16T15:02:02","slug":"incorporating-xml-into-your-database-objects","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/learn\/incorporating-xml-into-your-database-objects\/","title":{"rendered":"Incorporating XML into your Database Objects"},"content":{"rendered":"
\n

In my last two articles, “Working with the XML Data Type in SQL Server<\/a>” and “The XML Methods in SQL Server<\/a>,” I discussed how to use the XML<\/strong> 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<\/strong> 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. <\/p>\n

Creating Views <\/h2>\n

When you create a view, you can include an XML<\/strong> column just like you would a column configured with another data type. For example, the Sales.Store<\/strong> table in the AdventureWorks2008R2<\/strong> sample database includes the Demographics<\/strong> column, which is configured with the XML<\/strong> 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. <\/p>\n

In the following example, I create a view that retrieves data from the Sales.Store<\/strong> table, including the Demographics<\/strong> column: <\/p>\n

USE AdventureWorks2008R2;\n\nIF OBJECT_ID('StoreSurvey') IS NOT NULL\nDROP VIEW StoreSurvey;\nGO\n\nCREATE VIEW StoreSurvey\nAS\n  SELECT\n    BusinessEntityID AS StoreID,\n    Demographics AS Survey\n  FROM\n    Sales.Store;\nGO\n\nSELECT Survey\nFROM StoreSurvey\nWHERE StoreID = 292; <\/pre>\n
There should be no surprises here. I simply specified the name of the XML<\/strong> column as I would other columns and assigned the alias Survey<\/strong> to the column name. The SELECT<\/strong> statement I tagged onto the view definition returns the following results: <\/p>\n
<StoreSurvey xmlns=\"http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/adventure-works\/StoreSurvey\"\n  <AnnualSales>800000<\/AnnualSales>\n  <AnnualRevenue>80000<\/AnnualRevenue>\n  <BankName>United Security<\/BankName>\n  <BusinessType>BM<\/BusinessType>\n  <YearOpened>1996<\/YearOpened>\n  <Specialty>Mountain<\/Specialty>\n  <SquareFeet>21000<\/SquareFeet>\n  <Brands>2<\/Brands>\n  <Internet>ISDN<\/Internet>\n  <NumberEmployees>13<\/NumberEmployees>\n<\/StoreSurvey><\/pre>\n
The SELECT<\/strong> statement returns the full XML document, as it’s stored in the Demographics<\/strong> column (for the row with a StoreID<\/strong> value of 292<\/strong>). Although this all fine enough, it’s not particularly noteworthy. Where things get interesting is when you use an XML<\/strong> method within a view to retrieve only part of the XML data. For example, in the following view definition, I use the value(<\/strong>)<\/strong> method to retrieve a single value from the XML document in the Demographics<\/strong> column: <\/p>\n
USE AdventureWorks2008R2;\n\nIF OBJECT_ID('StoreSales') IS NOT NULL\nDROP VIEW StoreSales;\nGO\n\nCREATE VIEW StoreSales\nAS\n  SELECT\n    BusinessEntityID AS StoreID,\n    Demographics.value('declare namespace ns=\n      \"http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/adventure-works\/StoreSurvey\"; \n      (\/ns:StoreSurvey\/ns:AnnualSales)[1]', 'int') AS AnnualSales\n  FROM\n    Sales.Store;\nGO\n\nSELECT AnnualSales\nFROM StoreSales\nWHERE StoreID = 292; <\/pre>\n
The value(<\/strong>)<\/strong> method retrieves the value stored in the <AnnualSales><\/strong> child element of the <StoreSurvey><\/strong> element. Because the Demographics<\/strong> column is a typed XML<\/strong> column, the value(<\/strong>)<\/strong> 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<\/strong>. The SELECT<\/strong> statement I’ve tagged onto the view definition now returns only a single value, 800000<\/strong>. <\/p>\n
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. <\/p>\n
Creating Functions <\/h2>\n
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<\/strong> column based on an inputted store ID value (which is equivalent to the table’s BusinessEntityID<\/strong> value): <\/p>\n
USE AdventureWorks2008R2;\n\nIF OBJECT_ID('Survey') IS NOT NULL\nDROP FUNCTION Survey;\nGO\n\nCREATE FUNCTION Survey(@StoreID INT)\nRETURNS XML\nAS BEGIN\nRETURN\n(\n  SELECT Demographics\n  FROM Sales.Store\n  WHERE BusinessEntityID = @StoreID\n)\nEND;\nGO\n\nSELECT dbo.Survey(292); <\/pre>\n
Once again, no surprises here. When I call the function and store ID (292) in a SELECT<\/strong> statement, the function returns the full XML document from the Demographics<\/strong> column for that store. However, notice that I specified XML<\/strong> as the return type. Because XML is being returned, you can even use the XML<\/strong> methods when you call the function. For instance, in the following example, I again call the Survey<\/strong> function, passing in the store ID (292), but this time I also include the value()<\/strong> method: <\/p>\n
SELECT dbo.Survey(292).value('declare namespace ns=\n  \"http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/adventure-works\/StoreSurvey\"; \n  (\/ns:StoreSurvey\/ns:AnnualSales)[1]', 'int'); <\/pre>\n
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<\/strong>. <\/p>\n
I could have just as easily specified the query(<\/strong>)<\/strong> method, as in the following example: <\/p>\n
SELECT dbo.Survey(292).query('declare namespace ns=\n  \"http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/adventure-works\/StoreSurvey\"; \n  \/ns:StoreSurvey\/ns:AnnualSales');<\/pre>\n
Now the SELECT<\/strong> statement returns the entire <AnnualSales><\/strong> element, along with the namespace information, as shown in the following results: <\/p>\n
<ns:AnnualSales xmlns:ns=\"http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/adventure-works\/StoreSurvey\">800000<\/ns:AnnualSales> <\/pre>\n
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<\/strong> 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><\/strong> child element, just like I did earlier in my view definition: <\/p>\n
USE AdventureWorks2008R2;\n\nIF OBJECT_ID('AnnualSales') IS NOT NULL\nDROP FUNCTION AnnualSales;\nGO\n\nCREATE FUNCTION AnnualSales(@survey XML)\nRETURNS INT\nAS BEGIN\nRETURN @survey.value('declare namespace ns=\n  \"http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/adventure-works\/StoreSurvey\"; \n  (\/ns:StoreSurvey\/ns:AnnualSales)[1]', 'int')\nEND;\nGO\n\nSELECT dbo.AnnualSales(Demographics) AS AnnualSales\nFROM Sales.Store\nWHERE BusinessEntityID = 292; <\/pre>\n
There are a couple things worth noting about this function definition. First, unlike the preceding example, I specified INT<\/strong> as the return type (rather than XML<\/strong>) because INT<\/strong> is the value type returned by the value(<\/strong>)<\/strong> method. In addition, I specified XML<\/strong> as the type for the input parameter (@survey<\/strong>). I then used that parameter to call the value(<\/strong>)<\/strong> method. That way, I can pass in the column name as the argument to the function, and the value(<\/strong>)<\/strong> method will return the specific value from the XML document in the column. <\/p>\n
To verify this, I created a SELECT<\/strong> statement that calls the function and passes in the Demographics<\/strong> column as its input argument. Now the function returns a value of 800000<\/strong>, without having to specify an XML<\/strong> method when calling the function itself, as in the preceding example. <\/p>\n
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. <\/p>\n
Creating Computed Columns <\/h2>\n
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: <\/p>\n
USE AdventureWorks2008R2;\n\nIF OBJECT_ID('Stores') IS NOT NULL\nDROP TABLE Stores;\nGO\n\nCREATE TABLE Stores\n(\n  StoreID INT PRIMARY KEY,  \n  SurveyXml XML,\n  SurveyChar AS CAST(SurveyXml AS NVARCHAR(MAX))\n);\nGO\n\nINSERT INTO Stores(StoreID, SurveyXml)\nSELECT BusinessEntityID, Demographics\nFROM Sales.Store;\n\nSELECT * FROM Stores\nWHERE StoreID = 292; <\/pre>\n
First, I create the Stores<\/strong> table, which includes one XML<\/strong> column and one calculated column. The calculated column is simply a recasting of the XML<\/strong> column to an NVARCHAR(MAX)<\/strong> column. Notice in the INSERT<\/strong> statement, I retrieve data from the Sales.Store <\/strong>column, including the Demographics<\/strong> column. The Demographics<\/strong> data is converted to NVARCHAR(MAX)<\/strong> when inserted into the SURVEYCHAR<\/strong> column. As you would expect, the SELECT<\/strong> statement returns the full XML document for the SurveyXml <\/strong>column and returns the same document as character data for the SurveyChar<\/strong> column. <\/p>\n
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. <\/p>\n
It turns out that you can’t use XML<\/strong> 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: <\/p>\n
USE AdventureWorks2008R2;\n\nIF OBJECT_ID('Stores') IS NOT NULL\nDROP TABLE Stores;\nGO\n\nIF OBJECT_ID('AnnualSales') IS NOT NULL\nDROP FUNCTION AnnualSales;\nGO\n\nCREATE FUNCTION AnnualSales(@survey XML)\nRETURNS INT\nAS BEGIN\nRETURN @survey.value('declare namespace ns=\n  \"http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/adventure-works\/StoreSurvey\"; \n  (\/ns:StoreSurvey\/ns:AnnualSales)[1]', 'int')\nEND;\nGO\n\nCREATE TABLE Stores\n(\n  StoreID INT PRIMARY KEY,  \n  Survey XML,\n  SalesAmount AS dbo.AnnualSales(Survey)\n);\nGO\n\nINSERT INTO Stores(StoreID, Survey)\nSELECT BusinessEntityID, Demographics\nFROM Sales.Store;\n\nSELECT * FROM Stores\nWHERE StoreID = 292; <\/pre>\n
First, I create a function (AnnualSales<\/strong>) that uses the value()<\/strong> method to retrieve the <AnnualSales><\/strong> value from the inputted XML document, as you saw in a previous example. I then create a table that includes an XML<\/strong> column (Survey<\/strong>) and a calculated column (SalesAmount<\/strong>). Within the SalesAmount<\/strong> column definition, I call the AnnualSales<\/strong> function and pass in the Survey<\/strong> column as its argument. Next, I populate the table with data from the Sales.Store <\/strong>table and then use a SELECT<\/strong> statement to retrieve a row from that table. As to be expected, the Survey<\/strong> column returns the full XML document, and the SalesAmount<\/strong> column returns a value of 800000<\/strong>. <\/p>\n
This is, of course, a roundabout way to use XML<\/strong> 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. <\/p>\n
Creating Check Constraints <\/h2>\n
Another way you can work with XML in your database is to create check constraints on an XML<\/strong> column. However, chances are, if you decide to do that, you’ll want to use one of the XML<\/strong> 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: <\/p>\n
USE AdventureWorks2008R2;\n\nIF OBJECT_ID('Stores') IS NOT NULL\nDROP TABLE Stores;\nGO\n\nIF OBJECT_ID('SpecialtyExists') IS NOT NULL\nDROP FUNCTION SpecialtyExists;\nGO\n\nCREATE FUNCTION SpecialtyExists(@survey XML)\nRETURNS BIT\nAS BEGIN\nRETURN @survey.exist('declare namespace ns=\n  \"http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/adventure-works\/StoreSurvey\"; \n  \/ns:StoreSurvey\/ns:Specialty')\nEND;\nGO\n\nCREATE TABLE Stores\n(\n  StoreID INT PRIMARY KEY,  \n  Survey XML CHECK(dbo.SpecialtyExists(Survey) = 1)\n);\nGO\n\nINSERT INTO Stores(StoreID, Survey)\nSELECT BusinessEntityID, Demographics\nFROM Sales.Store;\n\nSELECT * FROM Stores\nWHERE StoreID = 292; <\/pre>\n
In this case, the function (SpecialtyExists<\/strong>) uses the exist(<\/strong>)<\/strong> method to determine whether the inputted XML document contains the <Specialty><\/strong> child element within the <StoreSurvey><\/strong> parent element. The EXIST()<\/strong> method will return 1<\/strong> if the child element exists within the parent element and will return a 0<\/strong> if the two elements don’t exist as specified. Consequently, the return type used for the function is BIT<\/strong>. As for the EXIST()<\/strong> method itself, it takes only one argument, the namespace reference and the parent and child elements. <\/p>\n
After I defined the function, I created a table (Stores<\/strong>), which includes an XML<\/strong> column (Survey<\/strong>). I’ve also defined a check constraint on the column. The constraint uses the SpecialtyExists<\/strong> function to determine if the data to be inserted into the Survey<\/strong> 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. <\/p>\n
For instance, when I inserted the data from the Sales.Store <\/strong>table, the Demographics<\/strong> 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><\/strong>, no rows would have been inserted because the check constraint would have evaluated to False<\/strong> in each case. <\/p>\n
As you saw with calculated columns, functions provide a workaround for using XML<\/strong> methods to create check constraints on XML<\/strong> columns. In the example above, I used the EXIST()<\/strong> 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<\/strong> method, although the EXIST()<\/strong> method will probably prove to be the handiest one in this case. <\/p>\n
Creating Defaults <\/h2>\n
If you create a table that includes an XML<\/strong> column, you can create a DEFAULT<\/strong> constraint on that column, as you would other column types. The main difference with the XML<\/strong> column, however, is that if that column is typed XML<\/strong>, your constraint can’t violate the schema associated with that column. <\/p>\n
You can create a DEFAULT<\/strong> constraint on an XML<\/strong> column in one of two ways: by implicitly converting the string value to XML<\/strong> or explicitly converting the value. In the following example, I use both approaches: <\/p>\n
USE AdventureWorks2008R2;\n\nIF OBJECT_ID('Stores') IS NOT NULL\nDROP TABLE Stores;\nGO\n\nCREATE TABLE Stores\n(\n  StoreID INT PRIMARY KEY,\n  Survey_implicit1 XML \n    DEFAULT '<Survey>Survey to follow<\/Survey>',\n  Survey_implicit2 XML \n    DEFAULT '<Survey>Survey to follow<\/Survey>',\n  Survey_explicit1 XML \n    DEFAULT CAST('<Survey>Survey to follow<\/Survey>' AS XML),\n  Survey_explicit2 XML \n    DEFAULT CAST('<Survey>Survey to follow<\/Survey>' AS XML)\n);\nGO\n\nINSERT INTO Stores\n  (StoreID, Survey_implicit1, Survey_explicit1)\nSELECT BusinessEntityID, Demographics, Demographics\nFROM Sales.Store;\n\nSELECT * FROM Stores\nWHERE StoreID = 292; <\/pre>\n
Notice that I created four XML<\/strong> 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<\/strong> columns (Survey_implicit1<\/strong> and Survey_implicit2<\/strong>) each include default definitions that specify an XML value as a string without trying to convert that value to the XML<\/strong> type. For the third and fourth columns (Survey_explicit1<\/strong> and Survey_explicit2<\/strong>), I specifically cast the columns to the XML<\/strong> type. Because SQL Server automatically converts the string values, you can take either approach. <\/p>\n
After I defined the table, I inserted data into the Survey_implicit1<\/strong> and Survey_explicit1<\/strong> columns. I then retrieved a row from the table. As to be expected, the Survey_implicit1<\/strong> and Survey_explicit1<\/strong> columns returned the entire XML documents and the Survey_implicit2<\/strong> and Survey_explicit2<\/strong> columns each returned the value defined in the DEFAULT<\/strong> constraint. <\/p>\n
Working with XML <\/h2>\n
As you saw with the DEFAULT<\/strong> 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<\/strong> 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. <\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
XML data can become a full participant in a SQL Server Database, and can be used in views, functions, check constraints, computed columns and defaults. Views and table-valued functions can be used to provide a tabular view of XML data that can be used in SQL Expressions.  Robert Sheldon explains how. …<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143525],"tags":[4168,5554,4149,4150,4151,4217],"coauthors":[],"class_list":["post-1262","post","type-post","status-publish","format-standard","hentry","category-learn","tag-database","tag-database-objects","tag-learn-sql-server","tag-sql","tag-sql-server","tag-xml"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1262","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1262"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1262\/revisions"}],"predecessor-version":[{"id":40620,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1262\/revisions\/40620"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1262"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1262"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1262"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1262"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}