My Sql Note: XML Methods

XML Methods (SQL Server 2008)

You can use the xml data type methods to query an XML instance stored in a variable or column of xml type.

The retrieving XML data is similar to retrieving objects configured with other data types. However, that’s true only when returning the entire XML value. If instead you want to work with individual components within an XML instance, such as when retrieving the value of a single attribute or element, you must use one of the five methods that available to the XML data type-query(), value(), exist(), nodes(), or modify().

Any operation on an XML document is applied on one or more elements or attributes at a specific location. To perform an operation, the location of the specific element or attribute has to be specified.

<BankName>United Security</BankName>

<Specialty>Mountain</Specialty>

<Comments>2nd largest bike store in region</Comments>

<Product>Mountain</Product>

<Product>Racing</Product>

</Products>

<Belt>leather</Belt>

<Product>Jerseys</Product>

<Product>Jackets</Product>

<Product>leather</Product>

<Product>Shorts</Product>

</Products>

</StoreSurvey>

XPath:

XPath is used for locating XML elements and attributes within an XML document and navigating through the XML tree. Every element and attribute within an XML document has a unique ‘‘path.’’

For example:

<Items>

</Items>

In the preceding example, the path to the first ItemNumber element is /Items/ItemNumber [1] and

the second is /Items/ItemNumber[2]. Each element and attribute within an XML document can be uniquely identified and processed using an XPath expression.

All the XML data type methods accept XPath expressions to specify the target element or attribute on which the given

Operation needs to be performed.

THE QUERY () METHOD:

The query () method retrieves a subset of untyped XML from the target XML instance. It’s probably the simplest and most straightforward of the XML methods. You need only specify the database object, the method name, and an XQuery expression, as shown in the following syntax:

DbObject.query ('XQuery')

You’ll notice that the XQuery expression is entirely enclosed in single quotes and those are enclosed in parentheses. The trick with any XML method is to get the XQuery expression right. But at its simplest, the expression must define the path of the element you want to retrieve.

In the following example, I use the query() method to return data from the <StoreSurvey> element in both the typed and untyped columns:

SELECT

Survey_untyped.query('/StoreSurvey') AS Info_untyped,

Survey_typed.query('declare namespace

ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";

/ns:StoreSurvey') AS Info_typed

FROM

Stores;

The XQuery expression for the Survey_Untyped column

For the untyped column, I specified the column name (Survey_untyped) followed by a period and then the method name (query). Within the parentheses and single quotes, I defined the XQuery expression (/StoreSurvey). That’s all there is to it. Because the <StoreSurvey> element is the root node, the entire XML document is returned.

The XQuery expression for the Survey_typed column

The XQuery expression for the Survey_typed column is a bit more complex. Because it is a typed column, the expression should be preceded by a namespace declaration. The namespace must be the same as the one referenced within the XML document stored in the typed column.

To declare a namespace, you specify the declare namespace keywords, followed by the alias name (in this case, ns). After the alias, you include an equal sign (=) and then the schema path and name. You then end the declaration with a semi-colon (;) to separate it from the main body of the XQuery expression. After you declare the namespace, you can use the alias within the expression to reference that namespace.

The expression itself is nearly identical to the one used for the untyped column, except that you must precede the element name with the namespace alias and a colon (ns:). As with the untyped column, the expression will return the entire XML document because it specifies only the root node.

Although the preceding example is helpful in demonstrating the basics of using the query() method, it’s not much use beyond that because you can just as easily retrieve all the column contents simply by specifying the column name. However, in the following example, I get more specific by limiting the results to the <AnnualSales> child element:

SELECT

Survey_untyped.query('/StoreSurvey/AnnualSales')

AS Info_untyped,

Survey_typed.query('declare namespace

ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";

/ns:StoreSurvey/ns:AnnualSales') AS Info_typed

FROM

Stores;

The only difference between this example and the preceding one is that I added /AnnualSales to the XQuery expression for the untyped column and /ns:AnnualSales to the expression for the typed column. The XQuery for the untyped column returns the following results.

And the XQuery for the typed column returns these results:

<ns:AnnualSales

xmlns:ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey">800000</ns:AnnualSales>

The primary difference between the two is that the data returned by the typed column includes the namespace information. The element values themselves (800000) are the same in both columns. If the <AnnualSales> element had included its own child elements, those too would have been displayed. For instance, the following example retrieves the <Products> elements, which are child elements of <StoreSurvey>, just like <AnnualSales>:

SELECT

Survey_untyped.query('/StoreSurvey/Products')

AS Products

FROM

Stores;

If you want to return a specific element and its child elements, you can do so by referencing one of its attributes. For example, suppose you want to include only products from the Bikes category. To do so, you can modify your XQuery expression as follows:

SELECT

Survey_untyped.query('/StoreSurvey/Products[@Type="Bikes"]')

AS BikeProducts

FROM

Stores;

Now the XQuery expression includes bracketed information that specifies the Type attribute and its value. The attribute name is preceded by the at (@) symbol, and the attribute value is preceded by an equal sign (=) and enclosed in double quotes. As a result, the SELECT statement now returns the following XML fragment:

<Product>Mountain</Product>

<Product>Racing</Product>

</Products>

Examples:

select

Survey_untyped.query('/StoreSurvey/AnnualSales') As 'AnnualSales'

from stores

select

Survey_untyped.query('/StoreSurvey/BankName') As 'BankName'

from stores

<BankName>United Security</BankName>

select

Survey_untyped.query('/StoreSurvey/Products') As 'Products'

from stores

<Product>Mountain</Product>

<Product>Racing</Product>

</Products>

<Belt>leather</Belt>

<Product>Jerseys</Product>

<Product>Jackets</Product>

<Product>leather</Product>

<Product>Shorts</Product>

</Products>

select

Survey_untyped.query('/StoreSurvey/Products[1]') As 'Products'

from stores

<Product>Mountain</Product>

<Product>Racing</Product>

</Products>

select

Survey_untyped.query('/StoreSurvey/Products[2]') As 'Products'

from stores

<Belt>leather</Belt>

<Product>Jerseys</Product>

<Product>Jackets</Product>

<Product>leather</Product>

<Product>Shorts</Product>

</Products>

select

Survey_untyped.query('/StoreSurvey/Products[@Type = "Clothes"] ') As 'Products'

from stores

<Belt>leather</Belt>

<Product>Jerseys</Product>

<Product>Jackets</Product>

<Product>leather</Product>

<Product>Shorts</Product>

</Products>

select

Survey_untyped.query('/StoreSurvey/Products[@Type = "Clothes"]/Product[1]') As 'Products'

from stores

<Product>Jerseys</Product>

select

Survey_untyped.query('/StoreSurvey/Products[@Type = "Clothes"]/Shoes[1]') As 'Products'

from stores

The VALUE () method:

The value () method returns a scalar value from the targeted XML document. The returned value is converted to the data type you specify when you call the method. The value() method makes it easier to work with individual values in order to compare or combine them, either with other XML values or values of different types.

As the following syntax illustrates, when you call the value() method, you must specify the XQuery expression and the Transact-SQL data type for the returned data:

DbObject.value('XQuery', 'SqlType')

For example, suppose you want to pull the amount of sales from the <AnnualSales> element in either the typed or untyped column in the Stores table. You can specify the path in the XQuery expression plus a numerical data type as the second argument, as shown in the following SELECT statement:

SELECT

Survey_untyped.value('(/StoreSurvey/AnnualSales)[1]', 'int')

AS Sales_untyped,

Survey_typed.value('declare namespace

ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";

(/ns:StoreSurvey/ns:AnnualSales)[1]', 'int') AS Sales_typed

FROM

Stores;

For the untyped column, the XQuery expression includes the element path as you saw in earlier examples. However, after the path, you must add an integer in brackets that indicates which element of that name you should retrieve. More often than not, you will simply add [1], as I’ve done here. This assures that, if there are multiple elements with the same name, only the first one will be returned. It also assures that only one element is being referenced at a time. In fact, even when there is only one element, as is the case with <AnnualSales>, you must still include the [1] because the value() method expects a singleton value.

The second argument passed into the value() method is the name of the data type, in this case, int. That means an int value will be returned by the method. Note that the method’s two arguments must each be enclosed in single quotes and separated with a comma.

As for the typed column, the namespace declaration and path are also similar to what you’ve seen in earlier examples. The only difference is that this expression must also include the [1], just as we did it for the untyped column. As a result, for each column, the SELECT statement returns a single int value of 800000, without any additional element information.

You can also retrieve an attribute value, rather than an element value, by specifying the name of the attribute in your element path. For example, the following SELECT statement retrieves the value of the Type attribute for the second instance of the <Products> element:

SELECT

Survey_untyped.value('(/StoreSurvey/Products/@Type)[2]', 'varchar(10)')

AS ProductType

FROM

Stores;

The expression includes [2], rather than [1], in order to retrieve data from the second instance of <Products>. As a result, the SELECT statement now returns the varchar value Clothes.

As stated earlier, the value() method is also handy if you want to combine or compare data. In the following example, I create a calculated column that’s based on two values returned from the untyped column:

SELECT

Survey_untyped.value('(/StoreSurvey/AnnualSales)[1]', 'int') -

Survey_untyped.value('(/StoreSurvey/AnnualRevenue)[1]', 'int')

AS Expenses

FROM

Stores;

The first instance of value() retrieves the <AnnualSales> value. The second value() instance retrieves the<AnnualRevenue> value. The second value is then subtracted from the first value to return a scalar value of720000.

You can also use XQuery functions in your expressions. For instance, in the following example, I use the concat function to add a string to the <Specialty> value:

SELECT

Survey_untyped.value('concat("Bike specialty: ",

(/StoreSurvey/Specialty)[1])', 'varchar(25)')

AS Specialty

FROM

Stores;

When you use the concat function, you specify each element that you want to concatenate as an argument to the function and separate those arguments with a comma. The statement returns the value Bike specialty: Mountain.

As you can see, the string has been concatenated with the <Specialty> value. I could have just as easily concatenated multiple element values or added more string values.

select Survey_untyped.value('(/StoreSurvey/Products)[1]','varchar(20)')

from stores

--------------------

MountainRoadRacing

select Survey_untyped.value('(/StoreSurvey/Products)[2]','varchar(20)')

from stores

--------------------

JerseysJacketsShorts

select Survey_untyped.value('(/StoreSurvey/Products[2]/Product)[1]','varchar(20)')

from stores

--------------------

Jerseys

The exist () method:

The exist () method lets you test for the existence of an element or one of its values. The method takes only one argument, the XQuery expression, as shown in the following syntax:

DbObject.exist('XQuery')

The key to using the exist() method is in understanding the values it returns. Unlike the query() and value()methods, the exist() method doesn’t return XML content. Rather, the method returns one of the following three values:

 A BIT value of 1 if the XQuery expression returns a nonempty result

 A BIT value of 0 if the XQuery expression returns an empty result.

 A NULL value if the XML data type instance is null.

A good way to test how the exist() method works is to use a variable to capture the method’s results, as I’ve done in the following example:

DECLARE @xml XML;

DECLARE @exist BIT;

SET @xml = (SELECT Survey_untyped FROM Stores);

SET @exist = @xml.exist('/StoreSurvey[BusinessType="BM"]');

SELECT @exist;

First, I declared the @xml variable as type XML. Then I declared the @exist variable as type BIT. I set the value of@xml to equal the XML document in the Survey_untyped column. I then used the exist() method on the @xmlvariable to test for the existence of /StoreSurvey[BusinessType="BM"] within the XML document. In other words, the exist() methods checks whether the <BusinessType> child element exists and whether it contains a value of “BM”.

I then assigned the results returned by the exist() method to the @exist variable and used a SELECT statement to return the contents of the variable. Because the XML document contains this child element and that element has a value of “BM”, the method returns a value of 1. However, if I change “BM” to “BMX”, as I’ve done in the following example, the method returns 0 because the element and value don’t exist exactly as specified:

DECLARE @xml2 XML;

DECLARE @exist2 BIT;

SET @xml2 = (SELECT Survey_untyped FROM Stores);

SET @exist2 = @xml2.exist('/StoreSurvey[BusinessType="BMX"]');

SELECT @exist2;

Now that you have a sense of how the exist() method works, let’s look at how you might use it in your queries.

In the following example, I include a WHERE clause that uses the exist() method to again test for the existence of/StoreSurvey[BusinessType="BM"]:

SELECT

Survey_untyped.query('/StoreSurvey/Products[@Type="Bikes"]')

AS BikeProducts

FROM

Stores

WHERE

Survey_untyped.exist('/StoreSurvey[BusinessType="BM"]') = 1;

The WHERE clause compares the results of the exist() method to the number 1. If the results equal 1, that is, if the XML contains the specified element and values, the WHERE clause evaluates to True and the SELECT statement returns the product information, as shown in the following results:

<Product>Mountain</Product>

<Product>Racing</Product>

</Products>

If the exist() method had specified a different <BusinessType> value, as it does in the following example, theSELECT statement would instead return an empty result set:

SELECT

Survey_untyped.query('/StoreSurvey/Products[@Type="Bikes"]')

AS BikeProducts

FROM

Stores

WHERE

Survey_untyped.exist('/StoreSurvey[BusinessType="BMX"]') = 1;

Again, the key to the exist() method is to remember that it returns only one of three values: 1, 0, or NULL.

DECLARE @xml2 XML;

DECLARE @exist2 BIT;

SET @xml2 = (SELECT Survey_untyped FROM Stores);

SET @exist2 = @xml2.exist('/StoreSurvey/Products[@Type = "Bikes"][Product= "Mountain"]');

SELECT @exist2;

O/P : 1

The nodes() method

The nodes () method can be a bit more slippery to understand than the other XML methods. To begin with, rather than returning XML or scalar values, the nodes () method returns what is essentially a table that includes one column. That means you should use the method only in those parts of a statement that can handle rowset views, such as the FROM clause. It also means that, when you call the nodes () method, you must assign a table alias and column alias to the rowset view returned by the method, as shown in the following syntax:

DbObject.nodes('XQuery') AS TableAlias(ColumnAlias)

The table and column aliases let you reference the rowset view in other parts of the statement. The method is useful when you want to shred an XML document, that is, decompose the document into a relational format. To better understand how this works, let’s look at an example that uses the Nodes () method on an XML variable:

DECLARE @bikes XML

SET @bikes =

'<Products>

<Product>Mountain</Product>

<Product>Racing</Product>

</Products>'

SELECT

Category.value('.','varchar(10)')

AS BikeTypes

FROM

@bikes.nodes('/Products/Product')

AS Bike(Category);

First, I declared the @bikes variable with the XML data type. Then I set its value to equal the XML fragment. In the FROM clause, I invoked the nodes() method on the variable to shred the <Product> elements. My goal was to retrieve the value from each instance of that element. I then provided a table alias (Bike) and a column alias (Category) to identify my rowset view. Now I essentially have a table with a single column that contains a row for each <Product> element.

When you use the nodes() method to create a rowset view of the XML data, you have to use one of the other XML methods to retrieve the contents of that view. In this case, I used the value() method in the SELECT list to return the value of each row. Notice that to retrieve the value I used a period to retrieve only the element values, as shown in the following results:

Mountain

Road

Racing

If you want to use the nodes() method to retrieve data from a table, you can use the CROSS APPLY operator in the FROM clause to associate the table with the rowset view returned by the method, as shown in the following example:

SELECT

Category.query('./text()')

AS BikeTypes

FROM

Stores CROSS APPLY

Survey_untyped.nodes('/StoreSurvey/Products[@Type="Bikes"]/Product') AS Bike(Category);

In this case, I specified the XQuery path as StoreSurvey/Products[@Type="Bikes"]/Product' in order to return only the bike-related products from the Survey_untyped column. However the SELECT list itself is the same as the preceding example, so this statement returns the same results as that example.

Joining XML nodes with relational tables:

SQL Server has extended the Relational Engine with XQuery capabilities. This offers a number of advantages — for example, the query processor can evaluate relational and XQuery operations in a single query. A single query plan is created with relational and XQuery operations, and results of a relational query can be joined with XQuery results and vice versa.

A T-SQL Developer can take advantage of this and write T-SQL queries that join XML nodes with relational columns.

The following code demonstrates a basic example:

SELECT

oh.OrderID,

c.Name AS Customer,

i.ItemDescription AS Item,

x.value('@Quantity','INT') AS Quantity,

x.value('@Price','MONEY') AS Price

FROM OrderHeader oh

INNER JOIN OrderXML ox ON

ItemData.value('(Order/@OrderID)[1]','INT') = oh.OrderID

CROSS APPLY ItemData.nodes('/Order/Item') o(x)

INNER JOIN Customers c ON c.CustomerID = oh.CustomerID

INNER JOIN Items i ON

i.ItemNumber=x.value('@ItemNumber','CHAR(4)')

The preceding example joins the OrderHeader table with the OrderXML table and this join is between a relational column and an XML node. Again, another join is established between the Items table and the Item elements returned by the nodes() method.

Using variables and filters in XQuery expressions

SQL Server allows only string literals as XQuery expressions. The following is illegal in SQL Server 2008:

DECLARE @node VARCHAR(100)

SELECT @node = ‘/Order/Item’)

SELECT

/* columns here */

FROM OrderXML

CROSS APPLY ItemData.nodes(@node) o(x)

Although you might want to use such an expression, it won’t work; I see this in XML forums all the time. Don’t make the same mistake. There are two common scenarios in which one might need to use variables in XQuery expressions:

■ To apply filters on the value of elements or attributes; for example, to retrieve the nodes with itemnumber = ‘‘Z001’’ or OrderID = ‘‘1’’

■ To retrieve the value of an element or attribute that is not known in advance, such as in cases where the name of the element or attribute is passed as an argument

SQL Server allows using variables as part of an XQuery expression using the sql:variable() function.

The following example uses a variable to filter an item number from the XML node:

DECLARE @ItemNumber CHAR(4)

SELECT @ItemNumber = 'D001'

SELECT

x.value('@ItemNumber','CHAR(4)') AS ItemNumber,

x.value('@Quantity','INT') AS Quantity,

x.value('@Price','MONEY') AS Price

FROM OrderXML

CROSS APPLY ItemData.nodes('/Order/Item[@ItemNumber=sql:variable("@ItemNumber")]') o(x)

ItemNumber Quantity Price

---------- ----------- ---------------------

D001 1 900.00

Returning the values of elements or attributes not known in advance is a little trickier. This can be achieved by using the XQuery function local-name() and by matching it with the value of the given variable:

DECLARE @Att VARCHAR(50)

SELECT @Att = 'ItemNumber'

SELECT

x.value('@*[local-name()=sql:variable("@Att")][1]','VARCHAR(50)') AS Value

FROM OrderXML

CROSS APPLY ItemData.nodes(’/Order/Item’) o(x)

Value

--------------------------------------------------

D001

Z001

D001

The preceding example retrieves the value of an attribute that is not known in advance. The name of the attribute is stored in a variable and the XQuery function local-name () is used to match the name of the attribute with the variable. Supporting variables as part of XQuery expressions greatly extends the power and flexibility of XQuery programming possibilities within T-SQL.
Accessing the parent node:

Most of the time, a query retrieving information from an XML document needs to access information from nodes at different levels in the XML tree. The easiest way to achieve this may be by using the parent node accessor, as shown in the following example:

SELECT

x.value(’../@OrderID’,’INT’) AS OrderID,

x.value(’@ItemNumber’,’CHAR(4)’) AS ItemNumber,

x.value(’@Quantity’,’INT’) AS Quantity,

x.value(’@Price’,’MONEY’) AS Price

FROM OrderXML

CROSS APPLY ItemData.nodes(’/Order/Item’) o(x)

OrderID ItemNumber Quantity Price

----------- ---------- ----------- ---------------------

1 D001 1 900.00

1 Z001 1 200.00

2 D001 1 900.00

The preceding example uses the parent node accessor (..) to retrieve the OrderID attribute. While this syntax is pretty simple and easy to use, it may not be good in terms of performance. When the parent node accessor is used, the XQuery processor needs to go backward to read the parent node information while processing each row, which might slow down the query.

The following example demonstrates a more optimized way of writing the preceding query using CROSS APPLY:

SELECT

h.value(’@OrderID’,’INT’) AS OrderID,

x.value(’@ItemNumber’,’CHAR(4)’) AS ItemNumber,

x.value(’@Quantity’,’INT’) AS Quantity,

x.value(’@Price’,’MONEY’) AS Price

FROM OrderXML

CROSS APPLY ItemData.nodes(’/Order’) o(h)

CROSS APPLY h.nodes(’Item’) i(x)

OrderID ItemNumber Quantity Price

----------- ---------- ----------- ---------------------

1 D001 1 900.00

1 Z001 1 200.00

2 D001 1 900.00

The first CROSS APPLY operator used in the preceding query retrieves an accessor to the Order element, and the second CROSS APPLY returns an accessor to each Item element. This eliminates the need to use the parent node accessor in the query to read information from the Order element.

The parent node accessor may be fine with small tables and small XML documents, but it is not recommended for large XML documents or tables. A better way of accessing the parent node is by using the CROSS APPLY approach demonstrated in the preceding example.
The modify () method

Modifies the contents of an XML document. Use this method to modify the content of an xml type variable or column. This method takes an XML DML statement to insert, update, or delete nodes from XML data. The modify() method of the xml data type can only be used in the SET clause of an UPDATE statement.

Syntax: modify (XML_DML)

XML_DML

Is a string in XML Data Manipulation Language (DML). The XML document is updated according to this expression.

An error is returned if the modify() method is called on a null value or results in a null value.

Adding Components in XML:

Inserts one or more nodes identified by Expression1 as child nodes or siblings of the node identified by Expression2.

Syntax

Insert

Expression1 (

{as first | as last} into | after | before

Expression2

)

Arguments

Expression1

Identifies one or more nodes to insert.

This can be a constant XML instance;

A reference to a typed XML data type instance of the same XML Schema collection on which the modify method is being applied;

An untyped XML data type instance using a stand-alone sql: column()/sql:variable() function; or an XQuery expression. The expression can result in a node, and also a text node, or in an ordered sequence of nodes. It cannot resolve to the root (/) node.

If the expression results in a value or a sequence of values, the values are inserted as a single text node with a space separating each value in the sequence. If you specify multiple nodes as constant, the nodes are included in parentheses and are separated by commas. You cannot insert heterogeneous sequences such as a sequence of elements, attributes, or values. If Expression1 resolves to an empty sequence, no insertion occurs and no errors are returned.

into

Nodes identified by Expression1 are inserted as direct descendents (child nodes) of the node identified by Expression2. If the node in Expression2 already has one or more child nodes, you must use either as first or as last to specify where you want the new node added. For example, at the start or at the end of the child list, respectively. The as first and as last keywords are ignored when attributes are inserted.

After

Nodes identified by Expression1 are inserted as siblings directly after the node identified by Expression2. The after keyword cannot be used to insert attributes. For example, it cannot be used to insert an attribute constructor or to return an attribute from an XQuery.

Before

Nodes identified by Expression1 are inserted as siblings directly before the node identified by Expression2. The before keyword cannot be used when attributes are being inserted. For example, it cannot be used to insert an attribute constructor or to return an attribute from an XQuery.

Expression2

Identifies a node. The nodes identified in Expression1 are inserted relative to the node identified by Expression2. This can be an XQuery expression that returns a reference to a node that exists in the currently referenced document. If more than one node is returned, the insert fails. If Expression2 returns an empty sequence, no insertion occurs and no errors are returned. If Expression2 is statically not a singleton, a static error is returned. Expression2 cannot be a processing instruction, comment, or attribute. Note that Expression2 must be a reference to an existing node in the document and not a constructed node.

Examples

A. Inserting element nodes into the document

USE AdventureWorks2008;

DECLARE @myDoc xml;

SET @myDoc =

'<Root>

</Features>

</ProductDescription>

</Root>' ;

SELECT @myDoc;

-- insert first feature child (no need to specify as first or as last)

SET @myDoc.modify('

insert <Maintenance>3 year parts and labor extended maintenance is available</Maintenance>

into (/Root/ProductDescription/Features)[1]') ;

SELECT @myDoc ;

-- insert second feature. We want this to be the first in sequence so use 'as first'

set @myDoc.modify('

insert <Warranty>1 year parts and labor</Warranty>

as first

into (/Root/ProductDescription/Features)[1]') ;

SELECT @myDoc ;

-- insert third feature child. This one is the last child of <Features> so use 'as last'

SELECT @myDoc

SET @myDoc.modify('

insert <Material>Aluminium</Material>

as last

into (/Root/ProductDescription/Features)[1]')

SELECT @myDoc ;

-- Add fourth feature - this time as a sibling (and not a child)

-- 'after' keyword is used (instead of as first or as last child)

SELECT @myDoc ;

set @myDoc.modify('

insert <BikeFrame>Strong long lasting</BikeFrame>

after (/Root/ProductDescription/Features/Material)[1]') ;

SELECT @myDoc;

-- Add fourth feature - this time as a sibling (and not a child)

-- 'before' keyword is used (instead of as first or as last child)

SELECT @myDoc ;

set @myDoc.modify('

insert <BikeColor>Red</BikeColor>

before (/Root/ProductDescription/Features/BikeFrame)[1]') ;

SELECT @myDoc;

<Root>

<Warranty>1 year parts and labor</Warranty>

<Maintenance>3 year parts and labor extended maintenance is available</Maintenance>

<Material>Aluminium</Material>

<BikeFrame>Strong long lasting</BikeFrame>

</Features>

</ProductDescription>

</Root>

Inserting multiple elements into the document

USE AdventureWorks;

DECLARE @myDoc xml;

SET @myDoc = N'<Root>

</ProductDescription>

</Root>';

DECLARE @newFeatures xml;

SET @newFeatures = N'<Warranty>1 year parts and labor</Warranty>

<Maintenance>3 year parts and labor extended maintenance is available</Maintenance>';

-- insert new features from specified variable

SET @myDoc.modify('

insert sql:variable("@newFeatures")

into (/Root/ProductDescription/Features)[1] ')

SELECT @myDoc;

<Root>

<Warranty>1 year parts and labor</Warranty>

<Maintenance>3 year parts and labor extended maintenance is available</Maintenance>

</Features>

</ProductDescription>

</Root>

Inserting attributes into a document

USE AdventureWorks2008;

DECLARE @myDoc xml ;

SET @myDoc =

'<Root>

<step>Manufacturing step 1 at this work center</step>

<step>Manufacturing step 2 at this work center</step>

</Location>

</Root>' ;

SELECT @myDoc ;

-- insert LaborHours attribute

SET @myDoc.modify('

insert attribute LaborHours {".5" }

into (/Root/Location[@LocationID=10])[1] ') ;

SELECT @myDoc ;

-- insert MachineHours attribute but its value is retrived from a sql variable @Hrs

DECLARE @Hrs float ;

SET @Hrs =.2 ;

SET @myDoc.modify('

insert attribute MachineHours {sql:variable("@Hrs") }

into (/Root/Location[@LocationID=10])[1] ');

SELECT @myDoc;

-- insert sequence of attribute nodes (note the use of ',' and ()

-- around the attributes.

SET @myDoc.modify('

insert (

attribute SetupHours {".5" },

attribute SomeOtherAtt {".2"}

)

into (/Root/Location[@LocationID=10])[1] ');

SELECT @myDoc;

<Root>

<step>Manufacturing step 1 at this work center</step>

<step>Manufacturing step 2 at this work center</step>

</Location>

</Root>

Inserting a comment node

USE AdventureWorks2008;

DECLARE @myDoc xml;

SET @myDoc =

'<Root>

<step>Manufacturing step 1 at this work center</step>

<step>Manufacturing step 2 at this work center</step>

</Location>

</Root>' ;

SELECT @myDoc;

SET @myDoc.modify('

insert 

after (/Root/Location[@LocationID=10]/step[1])[1] ');

SELECT @myDoc;

<Root>

<step>Manufacturing step 1 at this work center</step>

<step>Manufacturing step 2 at this work center</step>

</Location>

</Root>

Inserting a processing instruction

USE AdventureWorks2008;

DECLARE @myDoc xml;

SET @myDoc =

'<Root>

<step>Manufacturing step 1 at this work center</step>

<step>Manufacturing step 2 at this work center</step>

</Location>

</Root>' ;

SELECT @myDoc ;

SET @myDoc.modify('

insert <?Program = "Instructions.exe" ?>

before (/Root)[1] ') ;

SELECT @myDoc ;

<?Program = "Instructions.exe" ?>

<Root>

<step>Manufacturing step 1 at this work center</step>

<step>Manufacturing step 2 at this work center</step>

</Location>

</Root>

Inserting data using a CDATA section

USE AdventureWorks;

DECLARE @myDoc xml;

SET @myDoc =

'<Root>

</ProductDescription>

</Root>' ;

SELECT @myDoc ;

SET @myDoc.modify('

insert <![CDATA[ <notxml> as text </notxml> or cdata ]]>

into (/Root/ProductDescription/Features)[1] ') ;

SELECT @myDoc ;

<Root>

<Features> <notxml> as text </notxml> or cdata </Features>

</ProductDescription>

</Root>

Inserting text

USE AdventureWorks2008;

DECLARE @myDoc xml;

SET @myDoc = '<Root>

</Features>

</ProductDescription>

</Root>'

SELECT @myDoc;

set @myDoc.modify('

insert text{"Product Catalog Description"}

as first into (/Root)[1]

');

SELECT @myDoc;

<Root>Product Catalog Description<ProductDescription ProductID="1" ProductName="Road Bike"><Features /></ProductDescription></Root>

Inserting based on an if condition statement

USE AdventureWorks2008;

DECLARE @myDoc xml;

SET @myDoc =

'<Root>

<step>Manufacturing step 1 at this work center</step>

<step>Manufacturing step 2 at this work center</step>

</Location>

</Root>';

SELECT @myDoc

SET @myDoc.modify('

insert

if (/Root/Location[@LocationID=10])

then attribute MachineHours {".5"}

else ()

as first into (/Root/Location[@LocationID=10])[1] ');

SELECT @myDoc;

<Root>

<step>Manufacturing step 1 at this work center</step>

<step>Manufacturing step 2 at this work center</step>

</Location>

</Root>

Updating components in XML data -- replace value of (XML DML)

Updates the value of a node in the document.

replace value of Expression1

with Expression2

Expression1
Identifies a node whose value is to be updated. It must identify only a single node. That is, Expression1 must be a statical singleton. If the XML is typed, the type of the node must be a simple type. If multiple nodes are selected, an error is raised. If Expression1 returns an empty sequence, no value replacement occurs and no errors are returned. Expression1 must return a single element that has simply typed content (list or atomic types), a text node, or an attribute node. Expression1 cannot be a union type, a complex type, a processing instruction, a document node, or a comment node. If it is, an error is returned.

Expression2
Identifies the new value of the node. This can be an expression that returns a simply typed node, because data() will be used implicitly. If the value is a list of values, the update statement replaces the old value with the list. In modifying a typed XML instance, Expression2 must be the same type or a subtype of Expression1. Otherwise, an error is returned. In modifying an untyped XML instance, Expression2 must be an expression that can be atomized. Otherwise, an error is returned.

Replacing values in an XML instance

DECLARE @myDoc xml;

SET @myDoc = '<Root>

<Location LocationID="10"

LaborHours="1.1"

MachineHours=".2" >Manufacturing steps are described here.

<step>Manufacturing step 1 at this work center</step>

<step>Manufacturing step 2 at this work center</step>

</Location>

</Root>';

SELECT @myDoc;

-- update text in the first manufacturing step

SET @myDoc.modify('

replace value of (/Root/Location/step[1]/text())[1]

with "new text describing the manu step"

');

SELECT @myDoc;

-- update attribute value

SET @myDoc.modify('

replace value of (/Root/Location/@LaborHours)[1]

with "100.0"

');

SELECT @myDoc;

<Root>

<Location LocationID="10" LaborHours="100.0" MachineHours=".2">Manufacturing steps are described here.

<step>new text describing the manu step</step><step>Manufacturing step 2 at this work center</step></Location>

</Root>

Using the if expression to determine replacement value

DECLARE @myDoc xml

SET @myDoc = '<Root>

<Location LocationID="10"

LaborHours=".1"

MachineHours=".2" >Manu steps are described here.

<step>Manufacturing step 1 at this work center</step>

<step>Manufacturing step 2 at this work center</step>

</Location>

</Root>'

--SELECT @myDoc

SET @myDoc.modify('

replace value of (/Root/Location[1]/@LaborHours)[1]

with (

if (count(/Root/Location[1]/step) > 3) then

"3.0"

else

"1.0"

)

SELECT @myDoc

<Root>

<Location LocationID="10" LaborHours="1.0" MachineHours=".2">Manu steps are described here.

<step>Manufacturing step 1 at this work center</step><step>Manufacturing step 2 at this work center</step></Location></Root>

Updating XML stored in an untyped XML column

drop table T

CREATE TABLE T (i int, x xml)

INSERT INTO T VALUES(1,'<Root>

<Warranty>1 year parts and labor</Warranty>

<Maintenance>3 year parts and labor extended maintenance is available</Maintenance>

</Features>

</ProductDescription>

</Root>')

-- verify the current <ProductDescription> element

SELECT x.query(' /Root/ProductDescription')

FROM T

-- update the ProductName attribute value

UPDATE T

SET x.modify('

replace value of (/Root/ProductDescription/@ProductName)[1]

with "New Road Bike" ')

-- verify the update

SELECT x.query(' /Root/ProductDescription')

FROM T

<Warranty>1 year parts and labor</Warranty>

<Maintenance>3 year parts and labor extended maintenance is available</Maintenance>

</Features>

</ProductDescription>

Deleting components from XML data

Deletes nodes from an XML instance.

Syntax: delete Expression

Arguments

Expression

Is an XQuery expression identifying the nodes to be deleted. All the nodes selected by the expression, and also all the nodes or values that are contained within the selected nodes, are deleted. As described in insert (XML DML), this must be a reference to an existing node in the document. It cannot be a constructed node. The expression cannot be the root (/) node. If the expression returns an empty sequence, no deletion occurs and no errors are returned.

Deleting nodes from a document stored in an untyped xml variable

DECLARE @myDoc xml

SET @myDoc = '<?Instructions for=TheWC.exe ?>

<Root>

<Location LocationID="10"

LaborHours="1.1"

MachineHours=".2" >Some text 1

<step>Manufacturing step 1 at this work center</step>

<step>Manufacturing step 2 at this work center</step>

</Location>

</Root>'

SELECT @myDoc

-- delete an attribute

SET @myDoc.modify('

delete /Root/Location/@MachineHours

SELECT @myDoc

-- delete an element

SET @myDoc.modify('

delete /Root/Location/step[2]

SELECT @myDoc

-- delete text node (in <Location>

SET @myDoc.modify('

delete /Root/Location/text()

SELECT @myDoc

-- delete all processing instructions

SET @myDoc.modify('

delete //processing-instruction()

SELECT @myDoc

Final Output:

<Root>

<step>Manufacturing step 1 at this work center</step>

</Location>

</Root>

Deleting nodes from a document stored in an untyped xml column

CREATE TABLE T (i int, x xml)

INSERT INTO T VALUES(1,'<Root>

<Warranty>1 year parts and labor</Warranty>

<Maintenance>3 year parts and labor extended maintenance is available</Maintenance>

</Features>

</ProductDescription>

</Root>')

-- verify the contents before delete

SELECT x.query(' //ProductDescription/Features')

FROM T

-- delete the second feature

UPDATE T

SET x.modify('delete /Root/ProductDescription/Features/*[2]')

-- verify the deletion

SELECT x.query(' //ProductDescription/Features')

FROM T

Final Output:

<Warranty>1 year parts and labor</Warranty>

</Features>

Generating XML Output Using FOR XML

FOR XML is a row set aggregation function that returns a one-row, one-column result set containing an NVARCHAR(MAX) value. The TYPE directive can be used along with FOR XML to produce XML data type output instead of NVARCHAR(MAX).

FOR XML can be used with the AUTO, RAW, PATH and EXPLICIT directives to achieve different levels of control over the structure and format of the XML output.

FOR XML AUTO

FOR XML AUTO is one of the easiest options available to generate XML output from results of a SELECT query. It returns XML output having nested XML elements. Though it is easy to use and has a simple syntax, FOR XML AUTO does not provide much control over the structure of the XML output.

FOR XML AUTO, as the name suggests, ‘‘automatically’’ identifies the element names, hierarchies, and so on, based on the table name, aliases, and joins used in the query.

v The following example demonstrates a basic use of FOR XML AUTO:

SELECT OrderNumber, CustomerID

FROM OrderHeader

OrderNumber CustomerID

-------------------- -----------

SO101 1

SO102 1

SELECT OrderNumber, CustomerID

FROM OrderHeader

FOR XML AUTO

v The element name is determined based on the name or alias of the table. In the preceding example, an element named OrderHeader is created because the name of the table is OrderHeader. By adding an alias to the table name, a different element name can be generated:

SELECT OrderNumber, CustomerID

FROM OrderHeader o

FOR XML AUTO

v If the query has more than one table, FOR XML AUTO will generate hierarchical XML output based on the joins used in the query. The example given here joins the HumanResources.Employee table with the Person.Contact table:

SELECT Employee.EmployeeID, ContactInfo.FirstName,

ContactInfo.MiddleName, ContactInfo.LastName

FROM HumanResources.Employee AS Employee

INNER JOIN Person.Contact AS ContactInfo

ON ContactInfo.ContactID = Employee.ContactID

WHERE ContactInfo.FirstName = 'Rob'

</Employee>

</Employee>

These examples produce XML fragments, and not valid XML documents. A valid XML document can have only one top-level element. A root element can be added to the output of a FOR XML AUTO query by specifying the ROOT directive.

v The ROOT directive takes an optional argument that specifies the name of the root element. If this argument is not specified, the name of the top-level element will always be ‘‘root.’’ The following example adds a top-level element named Employees to the XML output:

SELECT Employee.EmployeeID, ContactInfo.FirstName,

ContactInfo.MiddleName, ContactInfo.LastName

FROM HumanResources.Employee AS Employee

INNER JOIN Person.Contact AS ContactInfo

ON ContactInfo.ContactID = Employee.ContactID

WHERE ContactInfo.FirstName = 'Rob'

FOR XML AUTO, ROOT ('Employees');

</Employee>

</Employee>

</Employees>

As you can see, the <Employee> element has been named automatically based on the table alias name. Notice too that the<ContactInfo> element is a child element of <Employee>. The structure of the elements is based on the order in which the columns are defined in the SELECT list and the tables that are specified in the FROM clause. In this case, because EmployeeID is the first column in the SELECT list and the Employee table is included in the FROM clause, the first element is <Employee>. And because the remaining columns, which are associated with the ContactInfo table, appear next in the SELECT list, they are added as a child element. If an additional table and its columns were included in the SELECT list, after the other columns, they would appear as a child element of <ContactInfo>.

v By default, FOR XML AUTO generates elements for each row, and values are generated as attributes. This behavior can be changed by specifying the ELEMENTS directive, which forces SQL Server to generate values as attributes:

SELECT Employee.EmployeeID, ContactInfo.FirstName,

ContactInfo.MiddleName, ContactInfo.LastName

FROM HumanResources.Employee AS Employee

INNER JOIN Person.Contact AS ContactInfo

ON ContactInfo.ContactID = Employee.ContactID

WHERE ContactInfo.FirstName = 'Rob'

FOR XML AUTO, ROOT ('Employees'), ELEMENTS;

<LastName>Walters</LastName>

</ContactInfo>

</Employee>

<MiddleName>T</MiddleName>

<LastName>Caron</LastName>

</ContactInfo>

</Employee>

</Employees>

Notice that the <ContactInfo> element also contains child elements, one for each column.

v If you want to include an element for columns with null values, you can use the XSINIL option, as you saw when using theRAW mode:

SELECT ContactInfo.FirstName, ContactInfo.MiddleName,

ContactInfo.LastName, Employee.EmployeeID

FROM HumanResources.Employee AS Employee

INNER JOIN Person.Contact AS ContactInfo

ON ContactInfo.ContactID = Employee.ContactID

WHERE ContactInfo.FirstName = 'Rob'

FOR XML AUTO, ROOT ('Employees'), ELEMENTS XSINIL;

<MiddleName xsi:nil="true" />

<LastName>Walters</LastName>

</Employee>

</ContactInfo>

<LastName>Caron</LastName>

</Employee>

</ContactInfo>

</Employees>

As you’ve seen in these examples, the XML is based on how the columns are listed in the SELECT list. However, as I mentioned earlier, the XML is also based on the tables listed in the FROM clause. In the preceding examples, the SELECT list contained only columns that are referenced in the FROM clause. If a column is not directly associated with a table in theFROM clause (as in a computed or aggregate column), the column is nested at the deepest level wherever it appears.

v For example, the following SELECT statement includes the Full Name computed column, which concatenates the first and last names:

SELECT Employee.EmployeeID,

(ContactInfo.FirstName + ' ' + ContactInfo.LastName) AS FullName,

ContactInfo.EmailAddress

FROM HumanResources.Employee AS Employee

INNER JOIN Person.Contact AS ContactInfo

ON ContactInfo.ContactID = Employee.ContactID

WHERE ContactInfo.FirstName = 'Rob'

FOR XML AUTO, ROOT ('Employees'), ELEMENTS XSINIL;

Because the FullName column appears in the SELECT list after the EmployeeID column, the FullName column is added as a child element of <Employee>, as shown in the following XML:

<FullName>Rob Walters</FullName>

<EmailAddress>rob0@adventure-works.com</EmailAddress>

</ContactInfo>

</Employee>

<FullName>Rob Caron</FullName>

<EmailAddress>rob1@adventure-works.com</EmailAddress>

</ContactInfo>

</Employee>

</Employees>

As I’ve mentioned, the placement of columns in the SELECT list impacts the resulting XML. This is also the case with computed columns.

For example, in the following SELECT statement, I’ve added the FullName column after theEmailAddress column:

SELECT Employee.EmployeeID, ContactInfo.EmailAddress,

(ContactInfo.FirstName + ' ' + ContactInfo.LastName) AS FullName

FROM HumanResources.Employee AS Employee

INNER JOIN Person.Contact AS ContactInfo

ON ContactInfo.ContactID = Employee.ContactID

WHERE ContactInfo.FirstName = 'Rob'

FOR XML AUTO, ROOT ('Employees'), ELEMENTS XSINIL;

Now the Full Name column will be added as a child element to the <ContactInfo> element, as the following XML demonstrates.

<EmailAddress>rob0@adventure-works.com</EmailAddress>

<FullName>Rob Walters</FullName>

</ContactInfo>

</Employee>

<EmailAddress>rob1@adventure-works.com</EmailAddress>

<FullName>Rob Caron</FullName>

</ContactInfo>

</Employee>

</Employees>

As these results show, you must be aware of the order you place columns when you define your SELECT list.

v Now let’s take a look at another aspect of the AUTO mode. One of the limitations of this mode (as well as the RAW mode) is that the column data is added as either attributes or child elements, depending on whether you specify the ELEMENTSoption. However, there might be times when you want to return some of the data as attributes and some as child elements. One method you can use with the AUTO mode is to return some of the data in a subquery. For example, the followingSELECT statement includes a subquery that returns the employee’s first and last names:

SELECT EmployeeID, LoginID,

(SELECT FirstName, LastName

FROM Person.Contact AS EmployeeName

WHERE EmployeeName.ContactID = Employee.ContactID

FOR XML AUTO, TYPE, ELEMENTS)

FROM HumanResources.Employee AS Employee

WHERE EmployeeID = 168

FOR XML AUTO;

Notice that the subquery includes a FOR XML clause that uses AUTO mode and includes the ELEMENTS option. The FOR XML clause also includes the TYPE option, which specifies that the data returned by the subquery be returned as the XML type. You must include the TYPE option to preserve the data as XML in the outer SELECT statement.

The outer SELECT statement also includes a FOR XML clause, but the ELEMENTS option is not included. As a result, only the first and last names will be returned as child elements, but the employee ID and login ID will be returned as attributes, as shown in the following XML:

<LastName>Caron</LastName>

</EmployeeName>

</Employee>

As you can see, subqueries let you maintain some control over the output. However, the AUTO mode (and the RAW mode, for that matter) provides little control over the XML returned by your query. For greater control, you’ll want to use the EXPLICITmode or the PATH mode

The RAW Mode:

The RAW mode generates a single XML element for each row in the result set returned by the query.

To use the FOR XML clause in RAW mode, you simply append the clause and RAW keyword to your SELECT statement, as shown in the following example:

SELECT e.EmployeeID, c.FirstName, c.MiddleName, c.LastName

FROM HumanResources.Employee e INNER JOIN Person.Contact c

ON c.ContactID = e.ContactID

WHERE c.FirstName = 'Rob'

FOR XML RAW;

Notice that the SELECT statement itself is a very basic query. (The statement pulls data from the AdventureWorks sample database.) Without the FOR XML clause, the statement would return the following results:

EmployeeID FirstName MiddleName LastName

---------- --------- ---------- --------

4 Rob NULL Walters

168 Rob T Caron

With the addition of the FOR XML clause, the statement returns the data as the following XML:

<row EmployeeID="4" FirstName="Rob" LastName="Walters" />

<row EmployeeID="168" FirstName="Rob" MiddleName="T" LastName="Caron" />

As you can see, each <row> element maps to a row that is returned by the SELECT statement, and each column, by default, is treated as an attribute of that element.

Note: You can include a FOR XML clause only in SELECT statements, if those statements define the outer, or top-level, query. However, you can also include the clause in INSERT, UPDATE, and DELETE statements that are part of a subquery.

In the preceding example, each element in the XML is named <row> by default. However, you can override the default behavior by providing a name for the element, as the following example shows:

SELECT e.EmployeeID, c.FirstName, c.MiddleName, c.LastName

FROM HumanResources.Employee e INNER JOIN Person.Contact c

ON c.ContactID = e.ContactID

WHERE c.FirstName = 'Rob'

FOR XML RAW ('Employee');

Now the element associated with each row returned by the query will be named <Employee>, rather than the default<row>:

In addition to being able to provide a name for the row element, you can also specify that a root element be created to wrap all other elements. To create a root element, add the ROOT keyword to your FOR XML clause:

SELECT e.EmployeeID, c.FirstName, c.MiddleName, c.LastName

FROM HumanResources.Employee e INNER JOIN Person.Contact c

ON c.ContactID = e.ContactID

WHERE c.FirstName = 'Rob'

FOR XML RAW ('Employee'), ROOT;

Notice that you must include a comma when adding an option such as ROOT in order to separate the elements. As the following results show, a <root> element is now included in the XML:

<root>

</root>

As with the row element, you can also provide a specific name for the root element:

SELECT e.EmployeeID, c.FirstName, c.MiddleName, c.LastName

FROM HumanResources.Employee e INNER JOIN Person.Contact c

ON c.ContactID = e.ContactID

WHERE c.FirstName = 'Rob'

FOR XML RAW ('Employee'), ROOT ('Employees');

In this case, I’ve named the root element <Employees>, as shown in the following results:

</Employees>

Up to this point, the examples I’ve shown you have added column values as attributes to each row element. This is the default behavior of the RAW mode. However, you can instead specify that the column values be added as child elements to the row element by including the ELEMENTS option in the FOR XML clause:

SELECT e.EmployeeID, c.FirstName, c.MiddleName, c.LastName

FROM HumanResources.Employee e INNER JOIN Person.Contact c

ON c.ContactID = e.ContactID

WHERE c.FirstName = 'Rob'

FOR XML RAW ('Employee'), ROOT ('Employees'), ELEMENTS;

Once again, I’ve added a comma to separate the options. As you can see in the following results, each <Employee>element now includes a set of child elements that correspond to the columns returned by the query:

<LastName>Walters</LastName>

</Employee>

<LastName>Caron</LastName>

</Employee>

</Employees>

Now the <Employee> elements no longer include any attributes and all data is rendered through individual child elements.

If you refer back to the XML returned by the previous example, you’ll notice that the data for employee 4 (Rob Walters) does not include a middle name. This is because that MiddleName value is null in the source data, and by default, no elements are created for a column whose value is null. However, you can override this behavior by adding the XSINIL keyword to the ELEMENTS option:

SELECT e.EmployeeID, c.FirstName, c.MiddleName, c.LastName

FROM HumanResources.Employee e INNER JOIN Person.Contact c

ON c.ContactID = e.ContactID

WHERE c.FirstName = 'Rob'

FOR XML RAW ('Employee'), ROOT ('Employees'), ELEMENTS XSINIL;

Now the results will include an element for the MiddleName column and will include the xsi:nil attribute with a value of true when a value is null, as shown in the following XML:

<MiddleName xsi:nil="true" />

<LastName>Walters</LastName>

</Employee>

<LastName>Caron</LastName>

</Employee>

</Employees>

Notice that the xmlns:xsi attribute has also been added to the root node and provides the name of the default schema instance.

Another important option that is supported by the RAW node is XMLSCHEMA, which specifies that an inline W3C XML Schema (XSD) be included in the XML data. You add the XMLSCHEMA option in the same way you add other options:

SELECT e.EmployeeID, c.FirstName, c.MiddleName, c.LastName

FROM HumanResources.Employee e INNER JOIN Person.Contact c

ON c.ContactID = e.ContactID

WHERE c.FirstName = 'Rob'

FOR XML RAW ('Employee'), ROOT ('Employees'), ELEMENTS XSINIL, XMLSCHEMA;

My Sql Note

Pages

Tuesday, 6 June 2017

XML Methods

No comments:

Post a Comment