Design a strategy to transform XML into relational data.
This objective may include but is not limited to: .nodes, .value, .query, XQuery and XPath
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.
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.
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.
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.
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.
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…
XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
, myQueryPlans AS (
dm_exec_query_plan.query_plan IS NOT NULL
, 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
query_plan.nodes('/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple[@StatementType="SELECT"]') AS myNodes(myNode)
myNode.nodes('//sp:RelOp') AS myOperations(myOperation)