70-451–MCITP–Designing an XML Strategy–Design a Strategy to Transform XML into Relational Data

Requirements

Design a strategy to transform XML into relational data.
This objective may include but is not limited to: .nodes, .value, .query, XQuery and XPath

My Take

In this section we will investigate how we query the XML data to retrieve relational data. Although we have seen some of this material previously, we will look at how the methods and language can be used to return information in a relational manner.

Using .nodes

The .nodes() method is useful when you want to shred a xml to return the relational data. The method takes an XQuery expression as well as the table name and column name to be returned and returns the defined output of type xml into the column and table defined. One record is returned for each node from the starting point defined by the XQuery expression. The nodes() statement consists of the following syntax:

Nodes (XQuery) as Table(Column)

Although it is not technically necessary, the .nodes() method is almost always called with the CROSS APPLY function. Once the nodes are returned in a relational format, you can apply any of the xml methods except the .modify to the output.

OpenXML

The .nodes() method is similar to the OPENXML rowset functionality, but generally faster than calling the sp_xml_preparedocument procedure, executing the select from openxml statement and finally calling the sp_xml_removedocument procedure. See the Using OPENXML article for more information on how to implement these procedures.

Using .value

The .value() method, as expressed in the Design a Strategy to Query and Modify XML Data post, is used to return a single value of the defined type. This method would probably be the most useful in relating xml to the referenced xml document as it returns the values as part of the “normal”, tabular result set. As would be expected, this value can be used in normal relational routines, such as filtering result sets, joining, aggregating, etc.

Using .query

The .query() method, as expressed in the Design a Strategy to Query and Modify XML Data post, is used to return an xml data type. This can be useful when you would like to return a portion of the xml document (or the whole document) along with the relational data.

XQuery and Xpath

XQuery and Xpath are used when transforming XML by passing them into the various xml methods. We have covered this in the Design a Strategy to Query and Modify XML Data post. We need to understand how both of these expression languages can be used to gather the data that is necessary to return the relational data. This will include understanding the methods to filter the data, to navigate to the nodes of choice, to use the XQuery functions , etc.

Example Query

In this example, we will use the .node(), .query() and .value() methods of the xml data type. We will look at the procedures stats dmv and use the query plan function to retrieve the estimated execution plan. This plan is an xml document that we can then poke around in to gather information using the queries options which we have been learning. I’m not at all saying that this query is useful, I’m more seeing what I can do to play around here…

WITH
XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
, myQueryPlans AS (
SELECT
dm_exec_procedure_stats.[object_id]
, dm_exec_procedure_stats.[execution_count]
, dm_exec_query_plan.query_plan
FROM
sys.[dm_exec_procedure_stats]
CROSS APPLY
sys.[dm_exec_query_plan](dm_exec_procedure_stats.[plan_handle])
WHERE
dm_exec_query_plan.query_plan IS NOT NULL
)
SELECT
[OBJECT_ID]
, [execution_count]
, myNode.query('.') AS myNode
, myNode.value('@StatementText', 'varchar(MAX)') AS myQuery
, myNode.value('@StatementOptmLevel', 'varchar(50)') AS OptimizationEffortLevel
, myOperation.value('@PhysicalOp', 'varchar(100)') AS myPhysicalOperation
, myOperation.value('@LogicalOp', 'varchar(100)') AS myLogicalOperation
, myOperation.value('@EstimateRows', 'numeric(38,2)') AS EstimatedRows
, myOperation.value('@EstimateIO', 'numeric(38,2)') AS EstimatedIO
FROM
[myQueryPlans]
CROSS APPLY
query_plan.nodes('/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple[@StatementType="SELECT"]') AS myNodes(myNode)
CROSS APPLY
myNode.nodes('//sp:RelOp') AS myOperations(myOperation)
;

Advertisements
This entry was posted in SQL and tagged , , . Bookmark the permalink.

One Response to 70-451–MCITP–Designing an XML Strategy–Design a Strategy to Transform XML into Relational Data

  1. Pingback: MCITP 70-451 Links Page | Destination: Change

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s