Programming4us
         
 
 
SQL Server

Using XML in SQL Server 2008: Relational Data As XML - The FOR XML Modes (part 4) - EXPLICIT Mode

1/10/2011 11:47:44 AM

EXPLICIT Mode

FOR XML EXPLICIT is a powerful, oft-maligned, somewhat daunting mode of SQL Server XML production. It allows for the shaping of row data in any desirable XML structure, but the SQL required to produce it can easily end up being hundreds (or, in some cases, thousands) of lines long, leading to a potential maintenance headache.

With EXPLICIT mode, the query author is responsible for making sure the XML is well formed and that the rowset generated behind the scenes corresponds to a very particular format.

The FOR XML PATH statement renders FOR XML EXPLICIT obsolete except when you need to output column values as CDATA. This section therefore briefly covers the required query structure for and provides an example of this particular case.

Note

It’s not an easy task to understand EXPLICIT mode just by reading. Practice is essential. After you’ve succeeded in using it a few times, it will begin to feel like an intuitive, albeit complex, way of doing things.


Microsoft calls the relational structure behind EXPLICIT mode queries the universal table. The universal table has a hierarchical structure sometimes known as the adjacency list model. Put simply, this means that the first column in the table is the primary key, and the second column is a foreign key referencing it, creating a parent–child relationship between rows in the same table. XML similarly models this relationship through the nesting of elements because nodes contained inside other nodes also hold a parent–child relationship.

Each level of hierarchical depth in the universal table is created by a separate SELECT statement, and each SELECT is unioned to the next, producing the complete rowset. Some details on the table structure help make this clearer:

  • The first column in the universal table (think of it as the primary key) must be named Tag and hold an integer value. The value of Tag can be thought of as representing the depth of the node that will be produced.

  • The second column must be named Parent and must refer to a valid value of Tag, or null, in the case of the first branch.

  • The rest of the selected columns in the query are mapped either to attributes, subelements, or CDATA nodes, or they may be selected but not produced in the resultant XML.

Listing 8 shows a query that returns a universal table. Later, you can change it so that it returns XML by adding FOR XML EXPLICIT.

Listing 8. A Query That Generates the Universal Table Rowset Format
SELECT
1 as Tag,
NULL as Parent,
Reason.ScrapReasonId 'ScrapReason!1!ScrapReasonId!element',
Name 'ScrapReason!1!!cdata',
WorkOrderId 'WorkOrder!2!WorkOrderId',
NULL 'WorkOrder!2!ScrappedQuantity'
FROM Production.ScrapReason Reason
JOIN Production.WorkOrder WorkOrder
ON Reason.ScrapReasonId = WorkOrder.ScrapReasonID
WHERE Reason.ScrapReasonId = 12

UNION ALL

SELECT
2 as Tag,
1 as Parent,
Reason.ScrapReasonId,
NULL,
WorkOrderId,
ScrappedQty
FROM Production.ScrapReason Reason
JOIN Production.WorkOrder WorkOrder
ON Reason.ScrapReasonId = WorkOrder.ScrapReasonID
WHERE Reason.ScrapReasonId = 12

The first SELECT statement in the union must use a special column alias syntax that tells the XML generator how to shape each column. This is the syntax:

element_name!corresponding_Tag_value!attribute_or_subelement_name[!directive]


The following list explains each part of the preceding syntax:

  • element_name— The name of the generated element associated with each row.

  • corresponding_Tag_value— The value of Tag for the context rowset.

  • attribute_or_subelement_name— The name of the attribute or subelement associated with the column in the context row.

  • directive— An optional directive to the XML generator. The possible values are

    • element—When specified, tells the XML generator to produce the column associated with attribute_or_subelement_name as a subelement. (An attribute is produced by default.)

    • hide—Tells the XML generator not to show the associated column data at all in the produced XML. This may be needed if there is some side effect desired from selecting the column but the data does not need to be shown.

    • cdata—Tells the XML generator to output the associated column data as a CDATA section.

    • xml—Disables entitization of text data. This can lead to non-well-formed XML because the XML special characters (&, ', ", <, >) are output directly.

In all subsequent SELECT statements, the columns corresponding to the rowsets identified by Tag are selected according to the layout specified in the first SELECT.

Notice how in Listing 47.8, NULL is selected for WorkOrder!2!ScrappedQuantity. This is done because the value for that column will be filled in by the SELECTTag value of 2, as specified in corresponding_Tag_value. Likewise, ScrappedQty is selected only in the second SELECT statement (where NULLScrapReason!1!!cdata) because Name is selected in this column in the first SELECT. The primary key (ScrapReasonId) that is the common thread joining both sets of rows must be specified in both SELECT statements for this query to work. statement having a is supplied for

Now that you have an understanding of the universal table structure that must be built, the only thing left to do is add FOR XML EXPLICIT to the query in Listing 8 and then order the output according to the desired element hierarchy. Listing 9 illustrates the final query and its result.

Listing 9. Using FOR XML EXPLICIT
SELECT
1 as Tag,
NULL as Parent,
Reason.ScrapReasonId 'ScrapReason!1!ScrapReasonId!element',
Name 'ScrapReason!1!!cdata',
WorkOrderId 'WorkOrder!2!WorkOrderId',
NULL 'WorkOrder!2!ScrappedQuantity'
FROM Production.ScrapReason Reason
JOIN Production.WorkOrder WorkOrder
ON Reason.ScrapReasonId = WorkOrder.ScrapReasonID
WHERE Reason.ScrapReasonId = 12
UNION ALL
SELECT
2 as Tag,
1 as Parent,
Reason.ScrapReasonId,
NULL,
WorkOrderId,
ScrappedQty
FROM Production.ScrapReason Reason
JOIN Production.WorkOrder WorkOrder
ON Reason.ScrapReasonId = WorkOrder.ScrapReasonID
WHERE Reason.ScrapReasonId = 12
ORDER BY 'ScrapReason!1!ScrapReasonId!element', 'WorkOrder!2!WorkOrderId'
FOR XML EXPLICIT, ROOT('ScrappedWorkOrders')
go
<ScrappedWorkOrders>
<ScrapReason>
<ScrapReasonId>12</ScrapReasonId>
<![CDATA[Thermoform temperature too high]]>
<WorkOrder WorkOrderId="2573" ScrappedQuantity="14" />
</ScrapReason>
<ScrapReason>
<ScrapReasonId>12</ScrapReasonId>
<![CDATA[Thermoform temperature too high]]>
<WorkOrder WorkOrderId="4972" ScrappedQuantity="1" />
</ScrapReason>
<ScrapReason>
<ScrapReasonId>12</ScrapReasonId>
<![CDATA[Thermoform temperature too high]]>
<WorkOrder WorkOrderId="7771" ScrappedQuantity="6" />
</ScrapReason>
<ScrapReason>
<ScrapReasonId>12</ScrapReasonId>
<![CDATA[Thermoform temperature too high]]>
<WorkOrder WorkOrderId="9071" ScrappedQuantity="1" />
</ScrapReason>
<ScrapReason>
<ScrapReasonId>12</ScrapReasonId>
<![CDATA[Thermoform temperature too high]]>
<WorkOrder WorkOrderId="10274" ScrappedQuantity="1" />
</ScrapReason>
{...}
</ScrappedWorkOrders>


In the ORDER BY clause, you tell the XML generator to first produce ScrapReason elements and then nest the WorkOrder elements underneath them.

Like the other modes, FOR XML EXPLICIT supports the BINARY BASE64 keywords, although base-64 encoding is performed automatically by the parser, even if not specified.

The ROOT keyword can also be used, although not when specifying XMLDATA. XMLSCHEMA is not supported as of this writing. ELEMENTS and XSINIL are also not supported, probably because you can get along without them, thanks to the many shaping options available.

Other -----------------
- Programming with SQL Azure : WCF Data Services (part 1)
- Programming with SQL Azure : Connecting to SQL Azure (part 4) - Sqlcmd
- Programming with SQL Azure : Connecting to SQL Azure (part 3) - ODBC
- Programming with SQL Azure : Connecting to SQL Azure (part 2)
- Programming with SQL Azure : Connecting to SQL Azure (part 1) - ADO.NET
- Programming with SQL Azure : Application Deployment Factors
- SQL Server 2008: SQL Server Web Services - Building Web Services (part 3)
- SQL Server 2008: SQL Server Web Services - Building Web Services (part 2)
- SQL Server 2008: SQL Server Web Services - Building Web Services (part 1)
- SQL Server 2008: SQL Server Web Services
- SQL Server 2008: SQL Server Service Broker - Related System Catalogs
- SQL Azure Backup Strategies (part 2)
- SQL Azure Backup Strategies (part 1) - Copying a Database
- SQL Server 2008: Troubleshooting SSB Applications with ssbdiagnose.exe
- SQL Server 2008: Service Broker Routing and Security
- Migrating Databases and Data to SQL Azure (part 9)
- Migrating Databases and Data to SQL Azure (part 8)
- Understanding Service Broker Constructs (part 5)
- Understanding Service Broker Constructs (part 4) - Creating the Conversation Initiator
- Migrating Databases and Data to SQL Azure (part 7)
 
 
Most View
- Windows Server 2003 : Managing Security Configuration with Security Templates (part 2)
- Writing Your First Service in Visual Basic 2008 (part 3)
- Windows Server 2008 : Configuring Terminal Services (part 1)
- Exchange Server 2010 : Unified Messaging - The Basics of Telephony
- Sharepoint 2007 : Associate a Workflow with a List or Library
- First Stages of SEO : Benchmarking Current Indexing Status
- Windows Azure : Managing Access Control Service Resources (part 1)
- Windows Server Backup Volume Recovery (part 3) - System Volume Recovery Using Network Shared Folder Backups
- Search Engine Basics : Vertical Search Engines
- SharePoint 2010 : Implementing and Configuring Information Management Policies (part 3) - Viewing Information Management Usage Reports
Top 10
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 3) - Configuring Recipient Filtering
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 2)
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 1)
- Implementing Edge Services for an Exchange Server 2007 Environment : Installing and Configuring the Edge Transport Server Components
- What's New in SharePoint 2013 (part 7) - BCS
- What's New in SharePoint 2013 (part 6) - SEARCH
- What's New in SharePoint 2013 (part 6) - WEB CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 5) - ENTERPRISE CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 4) - WORKFLOWS
- What's New in SharePoint 2013 (part 3) - REMOTE EVENTS