Using EXPLICIT Mode
As described in the topic, Constructing XML Using FOR XML, RAW and AUTO mode do not provide much control over the shape of the XML generated from a query result. However, EXPLICIT mode provides the most flexibility in generating the XML you want from a query result.
The EXPLICIT mode query must be written in a specific way so that the additional information about the required XML, such as expected nesting in the XML, is explicitly specified as part of the query. Depending on the XML you request, writing EXPLICIT mode queries can be cumbersome. You may find that Using PATH Mode with nesting is a simpler alternative to writing EXPLICIT mode queries.
Because you describe the XML you want as part of the query in EXPLICIT mode, you must ensure that the generated XML is well formed and valid.
Rowset Processing in EXPLICIT Mode
The EXPLICIT mode transforms the rowset that results from the query execution into an XML document. In order for EXPLICIT mode to produce the XML document, the rowset must have a specific format. This requires that you write the SELECT query to produce the rowset, the universal table, with a specific format so the processing logic can then produce the XML you want.
First, the query must produce the following two metadata columns:
- The first column must provide the tag number, integer type, of the current element, and the column name must be Tag. Your query must provide a unique tag number for each element that will be constructed from the rowset.
- The second column must provide a tag number of the parent element, and this column name must be Parent. In this way, the Tag and the Parent column provide hierarchy information.
These metadata column values, together with the information in the column names, are used to produce the XML you want. Note that your query must provide column names in a specific way. Also note that a 0 or NULL in the Parent column indicates that the corresponding element has no parent. The element is added to the XML as a top-level element.
To understand how the universal table generated by a query is processed into generating XML result, assume that you have written a query that produces this universal table:
.gif "Sample universal table")
Note the following about this universal table:
- The first two columns are Tag and Parent and are meta columns. These values determine the hierarchy.
- The column names are specified in a certain way, as described later in this topic.
- In generating the XML from this universal table, the data in this table is partitioned vertically into column groups. The grouping is determined based on the Tag value and the column names. In constructing XML, the processing logic selects one group of columns for each row and constructs an element. The following applies in this example:
- For Tag column value 1 in the first row, the columns whose names include the same tag number, Customer!1!cid and Customer!1!name, form a group. These columns are used in processing the row, and you may have noticed that the shape of the generated element is <
Customer id=... name=...>. Column name format is described later in this topic. - For rows with Tag column value 2, columns Order!2!id and Order!2!date form a group that is then used in constructing elements, <
Order id=... date=... />. - For rows with Tag column value 3, columns OrderDetail!3!id!id and OrderDetail!3!pid!idref form a group. Each of these rows generates an element, <
OrderDetail id=... pid=...>, from these columns.
- For Tag column value 1 in the first row, the columns whose names include the same tag number, Customer!1!cid and Customer!1!name, form a group. These columns are used in processing the row, and you may have noticed that the shape of the generated element is <
- Note that in generating XML hierarchy, the rows are processed in order. The XML hierarchy is determined as shown in the following:
The first row specifies Tag value 1 and Parent value NULL. Therefore, the corresponding element, <
Customer> element, is added as a top-level element in the XML.<Customer cid="C1" name="Janine">The second row identifies Tag value 2 and Parent value 1. Therefore, the element, <
Order> element, is added as a child of the <Customer> element.<Customer cid="C1" name="Janine"> <Order id="O1" date="1/20/1996">The next two rows identify Tag value 3 and Parent value 2. Therefore, the two elements, <
OrderDetail> elements, are added as children of the <Order> element.<Customer cid="C1" name="Janine"> <Order id="O1" date="1/20/1996"> <OrderDetail id="OD1" pid="P1"/> <OrderDetail id="OD2" pid="P2"/>The last row identifies 2 as the Tag number and 1 as the Parent tag number. Therefore, another <
Order> element child is added to the <Customer> parent element.<Customer cid="C1" name="Janine"> <Order id="O1" date="1/20/1996"> <OrderDetail id="OD1" pid="P1"/> <OrderDetail id="OD2" pid="P2"/> </Order> <Order id="O2" date="3/29/1997"> </Customer>
To summarize, the values in the Tag and Parent meta columns, the information provided in the column names, and the correct ordering of the rows produce the XML you want when you use EXPLICIT mode.
Universal Table Row Ordering
In constructing the XML, the rows in the universal table are processed in order. Therefore, to retrieve the correct children instances associated with their parent, the rows in the rowset must be ordered so that each parent node is immediately followed by its children.
Specifying Column Names in a Universal Table
When writing EXPLICIT mode queries, column names in the resulting rowset must be specified by using this format. They provide transformation information including element and attribute names and other additional information, specified by using directives.
This is the general format:
ElementName!TagNumber!AttributeName!Directive
Following is the description of the parts of the format.
- ElementName
Is the resulting generic identifier of the element. For example, if Customers is specified as ElementName, the <Customers> element is generated.
- TagNumber
Is a unique tag value assigned to an element. This value, with the help of the two metadata columns, Tag and Parent, determines the nesting of the elements in the resulting XML.
AttributeName
Provides the name of the attribute to construct in the specified ElementName. This is the behavior if Directive is not specified.If Directive is specified and it is xml, cdata, or element, this value is used to construct an element child of ElementName, and the column value is added to it.
If you specify the Directive, the AttributeName can be empty. For example, ElementName!TagNumber!!Directive. In this case, the column value is directly contained by the ElementName.
Directive
Directive is optional and you can use it to provide additional information for construction of the XML. Directive has two purposes.One of the purposes is to encode values as ID, IDREF, and IDREFS. You can specify ID, IDREF, and IDREFS keywords as Directives. These directives overwrite the attribute types. This allows you to create intra-document links.
Also, you can use Directive to indicate how to map the string data to XML. The hide, element, elementxsinil, xml, xmltext, and cdata keywords can be used as the Directive. The hide directive hides the node. This is useful when you retrieve values only for sorting purposes, but you do not want them in the resulting XML.
The element directive generates a contained element instead of an attribute. The contained data is encoded as an entity. For example, the < character becomes <. For NULL column values, no element is generated. If you want an element generated for null column values, you can specify the elementxsinil directive. This will generate an element that has the attribute xsi:nil=TRUE.
The xml directive is the same as an element directive, except that no entity encoding occurs. Note that the element directive can be combined with ID, IDREF, or IDREFS, whereas the xml directive is not allowed with any other directive, except hide.
The cdata directive contains the data by wrapping it with a CDATA section. The content is not entity encoded. The original data type must be a text type such as varchar, nvarchar, text, or ntext. This directive can be used only with hide. When this directive is used, AttributeName must not be specified.
Combining directives between these two groups is allowed in most cases, but combining them among themselves is not allowed.
If the Directive and the AttributeName is not specified, for example, Customer!1, an element directive is implied, such as Customer!1!!element, and column data is contained in the ElementName.
If the xmltext directive is specified, the column content is wrapped in a single tag that is integrated with the rest of the document. This directive is useful in fetching overflow, unconsumed, XML data stored in a column by OPENXML. For more information, see Querying XML Using OPENXML.
If AttributeName is specified, the tag name is replaced by the specified name. Otherwise, the attribute is appended to the current list of attributes of the enclosing elements by putting the content at the beginning of the containment without entity encoding. The column with this directive must be a text type, such as varchar, nvarchar, char, nchar, text, or ntext. This directive can be used only with hide. This directive is useful in fetching overflow data stored in a column. If the content is not a well-formed XML, the behavior is undefined.
Examples
Following are examples that illustrate the use of EXPLICIT mode.
A. Retrieving employee information
This example retrieves employee ID and employee names for each employee. In the AdventureWorks database, the employeeID can be obtained from the Employee table. Employee names can be obtained from the Contact table. The ContactID column can be used to join the tables.
Assume that you want FOR XML EXPLICIT transformation to generate XML as shown in the following:
<Employee EmpID="1" >
<Name FName="Guy" LName="Gilbert" />
</Employee>
...
Because there are two levels in the hierarchy, you would write two SELECT queries and apply UNION ALL. This is the first query that retrieves values for the <Employee> element and its attributes. The query assigns 1 as Tag value for the <Employee> element and NULL as Parent, because it is the top-level element.
SELECT 1 as Tag,
NULL as Parent,
EmployeeID as [Employee!1!EmpID],
NULL as [Name!2!FName],
NULL as [Name!2!LName]
FROM HumanResources.Employee E, Person.Contact C
WHERE E.ContactID = C.ContactID
This is the second query. It retrieves values for the <Name> element. It assigns 2 as Tag value for the <Name> element and 1 as Parent tag value identifying <Employee> as the parent.
SELECT 2 as Tag,
1 as Parent,
EmployeeID,
FirstName,
LastName
FROM HumanResources.Employee E, Person.Contact C
WHERE E.ContactID = C.ContactID
You combine these queries with UNION ALL, apply FOR XML EXPLICIT, and specify the required ORDER BY clause. You must sort the rowset first by EmployeeID and then by name so that the NULL values in the name appear first. By executing the following query without the FOR XML clause, you can see the universal table generated.
This is the final query:
SELECT 1 as Tag,
NULL as Parent,
EmployeeID as [Employee!1!EmpID],
NULL as [Name!2!FName],
NULL as [Name!2!LName]
FROM HumanResources.Employee E, Person.Contact C
WHERE E.ContactID = C.ContactID
UNION ALL
SELECT 2 as Tag,
1 as Parent,
EmployeeID,
FirstName,
LastName
FROM HumanResources.Employee E, Person.Contact C
WHERE E.ContactID = C.ContactID
ORDER BY [Employee!1!EmpID],[Name!2!FName]
FOR XML EXPLICIT
This is the partial result:
<Employee EmpID="1">
<Name FName="Guy" LName="Gilbert" />
</Employee>
<Employee EmpID="2">
<Name FName="Kevin" LName="Brown" />
</Employee>
...
The first SELECT specifies names for columns in the resulting rowset. These names form two column groups. The group that has Tag value 1 in the column name identifies Employee as an element and EmpID as the attribute. The other column group has Tag value 2 in the column and identifies <Name> as the element and FName and LName as the attributes.
The following table shows the partial rowset generated by the query:
Tag Parent Employee!1!EmpID Name!2!FName Name!2!LName
----------- ----------- ---------------- -------------------
1 NULL 1 NULL NULL
2 1 1 Guy Gilbert
1 NULL 2 NULL NULL
2 1 2 Kevin Brown
1 NULL 3 NULL NULL
2 1 3 Roberto Tamburello
...
This is how the rows in the universal table are processed to produce the resulting XML tree:
The first row identifies Tag value 1. Therefore, the column group that has the Tag value 1 is identified, Employee!1!EmpID. This column identifies Employee as the element name. An <Employee> element is then created that has EmpID attributes. Corresponding column values are assigned to these attributes.
The second row has the Tag value 2. Therefore, the column group that has the Tag value 2 in the column name, Name!2!FName, Name!2!LName, is identified. These column names identify Name as element name. A <Name> element is created that has FName and LName attributes . Corresponding column values are then assigned to these attributes. This row identifies 1 as Parent. This element child is added to the previous <Employee> element.
This process is repeated for rest of the rows in the rowset. Note the importance of ordering the rows in the universal table so that FOR XML EXPLICIT can process the rowset in order and generate the XML you want.
B. Specifying the element directive
This example is similar to example A, except it generates element-centric XML as shown in the following:
<Employee EmpID=...>
<Name>
<FName>...</FName>
<LName>...</LName>
</Name>
</Employee>
The query remains the same, except you add the ELEMENT directive in the column names. Therefore, instead of attributes, the <FName> and <LName> element children are added to the <Name> element. Because the Employee!1!EmpID column does not specify the ELEMENT directive, EmpID is added as the attribute of the <Employee> element.
SELECT 1 as Tag,
NULL as Parent,
EmployeeID as [Employee!1!EmpID],
NULL as [Name!2!FName!ELEMENT],
NULL as [Name!2!LName!ELEMENT]
FROM HumanResources.Employee E, Person.Contact C
WHERE E.ContactID = C.ContactID
UNION ALL
SELECT 2 as Tag,
1 as Parent,
EmployeeID,
FirstName,
LastName
FROM HumanResources.Employee E, Person.Contact C
WHERE E.ContactID = C.ContactID
ORDER BY [Employee!1!EmpID],[Name!2!FName!ELEMENT]
FOR XML EXPLICIT
This is the partial result
<Employee EmpID="1">
<Name>
<FName>Guy</FName>
<LName>Gilbert</LName>
</Name>
</Employee>
<Employee EmpID="2">
<Name>
<FName>Kevin</FName>
<LName>Brown</LName>
</Name>
</Employee>
...
C. Specifying the elementxsinil directive
When you specify the ELEMENT directive to retrieve element-centric XML, if the column has a NULL value, the corresponding element is not generated by the EXPLICIT mode. You can optionally specify the ELEMENTXSINIL directive to request the generating element for NULL values where the xsi:nil attribute is set with the value TRUE.
The following query constructs XML that includes an employee address. For AddressLine2 and City columns, the column names specify the ELEMENTXSINIL directive. This generates the element for NULL values in the AddressLine2 and City columns in the rowset.
SELECT 1 as Tag,
NULL as Parent,
EmployeeID as [Employee!1!EmpID],
E.AddressID as [Employee!1!AddressID],
NULL as [Address!2!AddressID],
NULL as [Address!2!AddressLine1!ELEMENT],
NULL as [Address!2!AddressLine2!ELEMENTXSINIL],
NULL as [Address!2!City!ELEMENTXSINIL]
FROM HumanResources.EmployeeAddress E, Person.Address A
WHERE E.ContactID = A.ContactID
UNION ALL
SELECT 2 as Tag,
1 as Parent,
EmployeeID,
E.AddressID,
A.AddressID,
AddressLine1,
AddressLine2,
City
FROM HumanResources.EmployeeAddress E, Person.Address A
WHERE E.AddressID = A.AddressID
ORDER BY [Employee!1!EmpID],[Address!2!AddressID]
FOR XML EXPLICIT
This is the partial result:
<Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
EmpID="1" AddressID="61">
<Address AddressID="61">
<AddressLine1>7726 Driftwood Drive</AddressLine1>
<AddressLine2 xsi:nil="true" />
<City>Monroe</City>
</Address>
</Employee>
...
D. Constructing siblings using EXPLICIT mode
Assume that you want to construct XML that provides sales order information. Note that <SalesPerson> and <OrderDetail> elements are siblings. Each Order has one <OrderHeader> element, one <SalesPerson> element, and one or more <OrderDetail> elements.
<OrderHeader SalesOrderID=... OrderDate=... CustomerID=... >
<SalesPerson SalesPersonID=... />
<OrderDetail SalesOrderID=... LineTotal=... ProductID=... OrderQty=... />
<OrderDetail SalesOrderID=... LineTotal=... ProductID=... OrderQty=.../>
...
</OrderHeader>
<OrderHeader ...</OrderHeader>
The following EXPLICIT mode query constructs this XML. Note that the query specifies Tag values of 1 for the <OrderHeader> element, 2 for the <SalesPerson> element, and 3 for the <OrderDetail> element. Because <SalesPerson> and <OrderDetail> are siblings, the query specifies the same Parent tag value of 1 identifying the <OrderHeader> element.
SELECT 1 as Tag,
0 as Parent,
SalesOrderID as [OrderHeader!1!SalesOrderID],
OrderDate as [OrderHeader!1!OrderDate],
CustomerID as [OrderHeader!1!CustomerID],
NULL as [SalesPerson!2!SalesPersonID],
NULL as [OrderDetail!3!SalesOrderID],
NULL as [OrderDetail!3!LineTotal],
NULL as [OrderDetail!3!ProductID],
NULL as [OrderDetail!3!OrderQty]
FROM Sales.SalesOrderHeader
WHERE SalesOrderID=43659 or SalesOrderID=43661
UNION ALL
SELECT 2 as Tag,
1 as Parent,
SalesOrderID,
NULL,
NULL,
SalesPersonID,
NULL,
NULL,
NULL,
NULL
FROM Sales.SalesOrderHeader
WHERE SalesOrderID=43659 or SalesOrderID=43661
UNION ALL
SELECT 3 as Tag,
1 as Parent,
SOD.SalesOrderID,
NULL,
NULL,
SalesPersonID,
SOH.SalesOrderID,
LineTotal,
ProductID,
OrderQty
FROM Sales.SalesOrderHeader SOH,Sales.SalesOrderDetail SOD
WHERE SOH.SalesOrderID = SOD.SalesOrderID
AND (SOH.SalesOrderID=43659 or SOH.SalesOrderID=43661)
ORDER BY [OrderHeader!1!SalesOrderID], [SalesPerson!2!SalesPersonID],
[OrderDetail!3!SalesOrderID],[OrderDetail!3!LineTotal]
FOR XML EXPLICIT
This is the partial result:
<OrderHeader SalesOrderID="43659" OrderDate="2001-07-01T00:00:00" CustomerID="676">
<SalesPerson SalesPersonID="279" />
<OrderDetail SalesOrderID="43659" LineTotal="10.373000" ProductID="712" OrderQty="2" />
<OrderDetail SalesOrderID="43659" LineTotal="28.840400" ProductID="716" OrderQty="1" />
<OrderDetail SalesOrderID="43659" LineTotal="34.200000" ProductID="709" OrderQty="6" />
...
</OrderHeader>
<OrderHeader SalesOrderID="43661" OrderDate="2001-07-01T00:00:00" CustomerID="442">
<SalesPerson SalesPersonID="282" />
<OrderDetail SalesOrderID="43661" LineTotal="20.746000" ProductID="712" OrderQty="4" />
<OrderDetail SalesOrderID="43661" LineTotal="40.373000" ProductID="711" OrderQty="2" />
...
</OrderHeader>
E. Specifying the ID, IDREF directives
This example is the same as example C. The only difference is that the query specifies the ID, IDREF directives. These directives overwrite the types of the SalesPersonID attribute in the <OrderHeader> and <OrderDetail> elements. This forms intra-document links. You need the schema to see the overwritten types. Therefore, the query specifies the XMLDATA option in the FOR XML clause to retrieve the schema.
SELECT 1 as Tag,
0 as Parent,
SalesOrderID as [OrderHeader!1!SalesOrderID!id],
OrderDate as [OrderHeader!1!OrderDate],
CustomerID as [OrderHeader!1!CustomerID],
NULL as [SalesPerson!2!SalesPersonID],
NULL as [OrderDetail!3!SalesOrderID!idref],
NULL as [OrderDetail!3!LineTotal],
NULL as [OrderDetail!3!ProductID],
NULL as [OrderDetail!3!OrderQty]
FROM Sales.SalesOrderHeader
WHERE SalesOrderID=43659 or SalesOrderID=43661
UNION ALL
SELECT 2 as Tag,
1 as Parent,
SalesOrderID,
NULL,
NULL,
SalesPersonID,
NULL,
NULL,
NULL,
NULL
FROM Sales.SalesOrderHeader
WHERE SalesOrderID=43659 or SalesOrderID=43661
UNION ALL
SELECT 3 as Tag,
1 as Parent,
SOD.SalesOrderID,
NULL,
NULL,
SalesPersonID,
SOH.SalesOrderID,
LineTotal,
ProductID,
OrderQty
FROM Sales.SalesOrderHeader SOH,Sales.SalesOrderDetail SOD
WHERE SOH.SalesOrderID = SOD.SalesOrderID
AND (SOH.SalesOrderID=43659 or SOH.SalesOrderID=43661)
ORDER BY [OrderHeader!1!SalesOrderID!id], [SalesPerson!2!SalesPersonID],
[OrderDetail!3!SalesOrderID!idref],[OrderDetail!3!LineTotal]
FOR XML EXPLICIT, XMLDATA
This is the partial result. In the schema, note that the ID, IDREF directives have overwritten the data types of the SalesOrderID attribute in the <OrderHeader> and <OrderDetail> elements. If you remove these directives, the schema returns original types of these attributes.
<Schema name="Schema1" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">
<ElementType name="OrderHeader" content="mixed" model="open">
<AttributeType name="SalesOrderID" dt:type="id" />
<AttributeType name="OrderDate" dt:type="dateTime" />
<AttributeType name="CustomerID" dt:type="i4" />
<attribute type="SalesOrderID" />
<attribute type="OrderDate" />
<attribute type="CustomerID" />
</ElementType>
<ElementType name="SalesPerson" content="mixed" model="open">
<AttributeType name="SalesPersonID" dt:type="i4" />
<attribute type="SalesPersonID" />
</ElementType>
<ElementType name="OrderDetail" content="mixed" model="open">
<AttributeType name="SalesOrderID" dt:type="idref" />
<AttributeType name="LineTotal" dt:type="number" />
<AttributeType name="ProductID" dt:type="i4" />
<AttributeType name="OrderQty" dt:type="i2" />
<attribute type="SalesOrderID" />
<attribute type="LineTotal" />
<attribute type="ProductID" />
<attribute type="OrderQty" />
</ElementType>
</Schema>
<OrderHeader xmlns="x-schema:#Schema1" SalesOrderID="43659" OrderDate="2001-07-01T00:00:00" CustomerID="676">
<SalesPerson SalesPersonID="279" />
<OrderDetail SalesOrderID="43659" LineTotal="10.373000" ProductID="712" OrderQty="2" />
...
</OrderHeader>
...
F. Specifying the ID, IDREFS directives
An element attribute can be specified as an ID type attribute, and the IDREFS attribute can then be used to refer to it. This enables intra-document links and is similar to the primary key and foreign key relationships in relational databases.
This example illustrates how the ID and IDREFS directives can be used to create attributes of ID and IDREFS types. Because IDs cannot be integer values, the ID values in this example are converted. In other words, they are type casted. Prefixes are used for the ID values.
Assume that you want to construct XML as shown in the following:
<Customer CustomerID="C1" SalesOrderIDList=" O11 O22 O33..." >
<SalesOrder SalesOrderID="O11" OrderDate="..." />
<SalesOrder SalesOrderID="O22" OrderDate="..." />
<SalesOrder SalesOrderID="O33" OrderDate="..." />
...
</Customer>
The SalesOrderIDList attribute of the < Customer > element is a multivalued attribute that refers to the SalesOrderID attribute of the < SalesOrder > element. To establish this link, the SalesOrderID attribute must be declared of ID type, and the SalesOrderIDList attribute of the < Customer> element must be declared of IDREFS type. Because a customer can request several orders, the IDREFS type is used.
IDREFS also have more than one value. Therefore, you have to use a separate select clause that will reuse the same tag, parent, and key column information. The ORDER BY then has to ensure that the sequence of rows that make up the IDREFS values appears grouped together under their parent element.
This is the query that produces the XML you want. The query uses the ID, IDREFS directives to overwrite the types in the column names (SalesOrder!2!SalesOrderID!ID, Customer!1!SalesOrderIDList!IDREFS).
SELECT 1 as Tag,
0 as Parent,
C.CustomerID [Customer!1!CustomerID],
NULL [Customer!1!SalesOrderIDList!IDREFS],
NULL [SalesOrder!2!SalesOrderID!ID],
NULL [SalesOrder!2!OrderDate]
FROM Sales.Customer C
UNION ALL
SELECT 1 as Tag,
0 as Parent,
C.CustomerID,
'O-'+CAST(SalesOrderID as varchar(10)),
NULL,
NULL
FROM Sales.Customer C, Sales.SalesOrderHeader SOH
WHERE C.CustomerID = SOH.CustomerID
UNION ALL
SELECT 2 as Tag,
1 as Parent,
C.CustomerID,
NULL,
'O-'+CAST(SalesOrderID as varchar(10)),
OrderDate
FROM Sales.Customer C, Sales.SalesOrderHeader SOH
WHERE C.CustomerID = SOH.CustomerID
ORDER BY [Customer!1!CustomerID] ,
[SalesOrder!2!SalesOrderID!ID],
[Customer!1!SalesOrderIDList!IDREFS]
FOR XML EXPLICIT
G. Using the hide directive to hide elements and attributes from resulting XML
This example illustrates the use of the hide directive. This directive is useful when you want the query to return an attribute for ordering the rows in the universal table that is returned by the query, but you do not want that attribute in the final resulting XML document.
This query constructs this XML:
<ProductModel ProdModelID="19" Name="Mountain-100">
<Summary>
<SummaryDescription>
<Summary> element from XML stored in CatalogDescription column
</SummaryDescription>
</Summary>
</ProductModel>
This query generates the XML you want. The query identifies two column groups having 1 and 2 as Tag values in the column names.
This query uses the query() Method (xml Data Type) of the xml data type to query the CatalogDescription column of xml type in order to retrieve the summary description. The query also uses the value() Method (xml Data Type) of the xml data type to retrieve the ProductModelID value from the CatalogDescription column. This value is not required in the resulting XML, but is required to sort the resulting rowset. Therefore, the column name, [Summary!2!ProductModelID!hide], includes the hide directive. If this column is not included in the SELECT statement, you will have to sort the rowset by [ProductModel!1!ProdModelID] and [Summary!2!SummaryDescription] that is xml type and you cannot use the xml type column in ORDER BY. Therefore, the additional [Summary!2!ProductModelID!hide] column is added and is then specified in the ORDER BY clause.
SELECT 1 as Tag,
0 as Parent,
ProductModelID as [ProductModel!1!ProdModelID],
Name as [ProductModel!1!Name],
NULL as [Summary!2!ProductModelID!hide],
NULL as [Summary!2!SummaryDescription]
FROM Production.ProductModel
WHERE CatalogDescription is not null
UNION ALL
SELECT 2 as Tag,
1 as Parent,
ProductModelID,
Name,
CatalogDescription.value('
declare namespace PD="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
(/PD:ProductDescription/@ProductModelID)[1]', 'int'),
CatalogDescription.query('
declare namespace pd="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
/pd:ProductDescription/pd:Summary')
FROM Production.ProductModel
WHERE CatalogDescription is not null
ORDER BY [ProductModel!1!ProdModelID],[Summary!2!ProductModelID!hide]
FOR XML EXPLICIT
go
This is the result:
<ProductModel ProdModelID="19" Name="Mountain-100">
<Summary>
<SummaryDescription>
<pd:Summary xmlns:pd="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription" >
<p1:p xmlns:p1="http://www.w3.org/1999/xhtml">Our top-of-the-line competition mountain bike. Performance-enhancing options include the innovative HL Frame, super-smooth front suspension, and traction for all terrain. </p1:p>
</pd:Summary>
</SummaryDescription>
</Summary>
</ProductModel>
H. Specifying the element directive and the entity encoding
This example illustrates the difference between the element and xml directives. The element directive entitizes the data, but the xml directive does not. The <Summary> element is assigned XML, <Summary>This is summary description</Summary>, in the query.
Consider this query:
SELECT 1 as Tag,
0 as Parent,
ProductModelID as [ProductModel!1!ProdModelID],
Name as [ProductModel!1!Name],
NULL as [Summary!2!SummaryDescription!ELEMENT]
FROM Production.ProductModel
WHERE ProductModelID=19
UNION ALL
SELECT 2 as Tag,
1 as Parent,
ProductModelID,
NULL,
'<Summary>This is summary description</Summary>'
FROM Production.ProductModel
WHERE ProductModelID=19
FOR XML EXPLICIT
This is the result. The summary description is entitized in the result.
<ProductModel ProdModelID="19" Name="Mountain-100">
<Summary>
<SummaryDescription><Summary>This is summary description</Summary></SummaryDescription>
</Summary>
</ProductModel>
Now, if you specify the xml directive in the column name, Summary!2!SummaryDescription!xml, instead of the element directive, you will receive the summary description without entitization.
<ProductModel ProdModelID="19" Name="Mountain-100">
<Summary>
<SummaryDescription>
<Summary>This is summary description</Summary>
</SummaryDescription>
</Summary>
</ProductModel>
Instead of assigning a static XML value, the following query uses the query() method of the xml type to retrieve the product model summary description from the CatalogDescription column of xml type. Because the result is known to be of xml type, no entitization is applied.
SELECT 1 as Tag,
0 as Parent,
ProductModelID as [ProductModel!1!ProdModelID],
Name as [ProductModel!1!Name],
NULL as [Summary!2!SummaryDescription]
FROM Production.ProductModel
WHERE CatalogDescription is not null
UNION ALL
SELECT 2 as Tag,
1 as Parent,
ProductModelID,
Name,
(SELECT CatalogDescription.query('
declare namespace pd="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
/pd:ProductDescription/pd:Summary'))
FROM Production.ProductModel
WHERE CatalogDescription is not null
ORDER BY [ProductModel!1!ProdModelID],Tag
FOR XML EXPLICIT
I. Specifying the cdata directive
If the directive is set to cdata, the contained data is not entity encoded, but is put in the CDATA section. The cdata attributes must be nameless.
The following query wraps the product model summary description in a CDATA section.
SELECT 1 as Tag,
0 as Parent,
ProductModelID as [ProductModel!1!ProdModelID],
Name as [ProductModel!1!Name],
'<Summary>This is summary description</Summary>'
as [ProductModel!1!!cdata] -- no attribute name so ELEMENT assumed
FROM Production.ProductModel
WHERE ProductModelID=19
FOR XML EXPLICIT
This is the result:
<ProductModel ProdModelID="19" Name="Mountain-100">
<![CDATA[<Summary>This is summary description</Summary>]]>
</ProductModel>
J. Specifying the xmltext directive
This example illustrates how data in the overflow column is addressed by using the xmltext directive in a SELECT statement using EXPLICIT mode.
Consider the Person table. This table has an Overflow column that stores the unconsumed part of the XML document.
CREATE TABLE Person(PersonID varchar(5), PersonName varchar(20), Overflow nvarchar(200))
INSERT INTO Person VALUES ('P1','Joe',N'<SomeTag attr1="data">content</SomeTag>')
INSERT INTO Person VALUES ('P2','Joe',N'<SomeTag attr2="data"/>')
INSERT INTO Person VALUES ('P3','Joe',N'<SomeTag attr3="data" PersonID="P">content</SomeTag>')
This query retrieves columns from the Person table. For the Overflow column, AttributeName is not specified, but directive is set to xmltext as part of providing a universal table column name.
SELECT 1 as Tag, NULL as parent,
PersonID as [Parent!1!PersonID],
PersonName as [Parent!1!PersonName],
overflow as [Parent!1!!xmltext] -- No AttributeName; xmltext directive
FROM Person
FOR XML EXPLICIT
In the resulting XML document:
- Because AttributeName is not specified for the Overflow column and the xmltext directive is specified, the attributes in the <
overflow> element are appended to the attribute list of the enclosing <Parent> element. - Because the PersonID attribute in the <
xmltext> element conflicts with the PersonID attribute retrieved on the same element level, the attribute in the <xmltext> element is ignored, even if PersonID is NULL. Generally, an attribute overrides an attribute of the same name in the overflow.
This is the result:
<Parent PersonID="P1" PersonName="Joe" attr1="data">content</Parent>
<Parent PersonID="P2" PersonName="Joe" attr2="data"></Parent>
<Parent PersonID="P3" PersonName="Joe" attr3="data">content</Parent>
If the overflow data has subelements and the same query is specified, the subelements in the Overflow column are added as the subelements of the enclosing <Parent> element.
For example, change the data in the Person table so that the Overflow column now has subelements.
TRUNCATE TABLE Person
INSERT INTO Person VALUES ('P1','Joe',N'<SomeTag attr1="data">content</SomeTag>')
INSERT INTO Person VALUES ('P2','Joe',N'<SomeTag attr2="data"/>')
INSERT INTO Person VALUES ('P3','Joe',N'<SomeTag attr3="data" PersonID="P"><name>PersonName</name></SomeTag>')
If the same query is executed, the subelements in the <xmltext> element are added as subelements of the enclosing <Parent> element:
SELECT 1 as Tag, NULL as parent,
PersonID as [Parent!1!PersonID],
PersonName as [Parent!1!PersonName],
overflow as [Parent!1!!xmltext] -- no AttributeName, xmltext directive
FROM Person
FOR XML EXPLICIT
This is the result:
<Parent PersonID="P1" PersonName="Joe" attr1="data">content</Parent>
<Parent PersonID="P2" PersonName="Joe" attr2="data"></Parent>
<Parent PersonID="P3" PersonName="Joe" attr3="data">
<name>PersonName</name>
</Parent>
If AttributeName is specified with the xmltext directive, the attributes of the <overflow> element are added as attributes of the subelements of the enclosing <Parent> element. The name specified for AttributeName becomes the name of the subelement
In this query, AttributeName, <overflow>, is specified together with the xmltext directive*:*
SELECT 1 as Tag, NULL as parent,
PersonID as [Parent!1!PersonID],
PersonName as [Parent!1!PersonName],
overflow as [Parent!1!overflow!xmltext] -- overflow is AttributeName
-- xmltext is directive
FROM Person
FOR XML EXPLICIT
This is the result:
<Parent PersonID="P1" PersonName="Joe">
<overflow attr1="data">content</overflow>
</Parent>
<Parent PersonID="P2" PersonName="Joe">
<overflow attr2="data" />
</Parent>
<Parent PersonID="P3" PersonName="Joe">
<overflow attr3="data" PersonID="P">
<name>PersonName</name>
</overflow>
</Parent>
In this query element, directive is specified for PersonName attribute. This results in PersonName being added as a subelement of the enclosing <Parent> element. The attributes of the <xmltext> are still appended to the enclosing <Parent> element. The contents of the <overflow> element, subelements, are prepended to the other subelements of the enclosing <Parent> elements.
SELECT 1 as Tag, NULL as parent,
PersonID as [Parent!1!PersonID],
PersonName as [Parent!1!PersonName!element], -- element directive
overflow as [Parent!1!!xmltext]
FROM Person
FOR XML EXPLICIT
This is the result:
<Parent PersonID="P1" attr1="data">content<PersonName>Joe</PersonName>
</Parent>
<Parent PersonID="P2" attr2="data">
<PersonName>Joe</PersonName>
</Parent>
<Parent PersonID="P3" attr3="data">
<name>PersonName</name>
<PersonName>Joe</PersonName>
</Parent>
If the xmltext column data contains attributes on the root element, these attributes are not shown in the XML data schema and the MSXML parser does not validate the resulting XML document fragment. For example:
SELECT 1 as Tag,
0 as Parent,
N'<overflow a="1"/>' as 'overflow!1!!xmltext'
FOR XML EXPLICIT, xmldata
This is the result. Note that in the returned schema, the overflow attribute a is missing from the schema:
<Schema name="Schema2"
xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:datatypes">
<ElementType name="overflow" content="mixed" model="open">
</ElementType>
</Schema>
<overflow xmlns="x-schema:#Schema2" a="1">
</overflow>
See Also
Reference
Using RAW Mode
Using AUTO Mode
Constructing XML Using FOR XML