Showing posts with label XML. Show all posts
Showing posts with label XML. Show all posts

Sunday, 6 March 2011

Denormalizing A Column Into A String

If you wanted to take query results and turn them into a string, how would you go about it? Traditionally, you may use a dreaded cursor/while loop, or a best, a complex, poorly performing UDF. But since the introduction SQL 2005 and native XML, there is a nice little trick you can use.

As an example, I am going to use a scenario from a question I answered on MSDN. Basically, the requirement was to take a list of values, turn them into a string, and then insert the average value into the middle. So the first thing I am going to do, is use a Table Variable to generate the list...

DECLARE @values table(col1 float)

INSERT INTO @values VALUES(2), (2), (3), (4), (5)

...Next, I am going to find the average value, and insert this into the list. For simplicity, I will use a second table variable...

DECLARE @values2 table(col1 float)

INSERT INTO @values2
SELECT AVG(col1) AS col1 FROM @values
UNION ALL
SELECT col1 FROM @values
ORDER BY col1


...So, now I have the list of values I need, pre-sorted in a table variable, It's time for the interesting bit. Basically, I am going to use the data() X-Query function, which returns a typed value for each item specified, inside a sub-query, with a FOR XML clause. The outer query will have no FOR XML clause, so the results will be returned as relational data...

SELECT DISTINCT ConCat_Column =
(
SELECT CAST(col1 as decimal(3,2))  AS [data()]
FROM @values2
FOR XML PATH ('')
)


...The results are as follows. I have highlighted the average value in bold...

2.00 2.00 3.00 3.20 4.00 5.00

...Very cool eh?

Sunday, 19 December 2010

Shredding XML with OPENXML & Nodes Part III

In Parts I & II, I looked at the different ways of shredding XML into relational data. The only question that remains now, is when should you use each one?
Well, firstly, there is a scenario in which the nodes method is the only way forward; and that is if you want to shred data from multiple rows within a table. For example, imagine you have created a table using the following code...


CREATE TABLE Products(
ProductID   int IDENTITY(1,1),
ProductDescription      nvarchar(100),
ProductReviews    XML)
ON [PRIMARY]

…If this table was holding the following data…

clip_image001

…How would you go about extracting a relational list of comments? Well you could not use the OPENXML function, because you would need to call it once for each row, so the trick is, to use the Nodes method, with the T-SQL CROSS APPLY operator.
If you ran the following code…


SELECT ProductDescription,
       TempCol.value('text()[1]', 'NVARCHAR(500)') Review
FROM Products
CROSS APPLY ProductReviews.nodes('/Review/Comments') AS
TempTable(TempCol)

…You would see this output…

clip_image002

…Here, we are using the nodes and value methods in the same way as we were in Part II. The only differences are that we are now implementing CROSS APPLY, which is calling the nodes method against every row in the result set, and passing the results into our temporary table structure.
Secondly, that because we are reading data from an element, as opposed to an attribute, we are using the text() function, instead of using @attribute.
But what about performance? Are there any performance considerations to take into account, if we are shredding a single variable, and hence have the choice of either Nodes or OPENXML?
Well, if we run the OPENXML code from Part I again, but this time prefixing it with the following statement…


SET STATISTICS TIME ON
GO

We will see the following statistics in the Messages Tab of the Results Pane…


…Giving us a total execution time of 540ms, with a total CPU time of 20ms.
If we ran the equivalent code using the Nodes method however, (see Part II) we would get the following statistics…   

clip_image004

…Here, you will notice a total execution time of just 113ms, but at the expense of 90ms CPU time. (Over 4 times quicker, but at a cost of more than 4 times the pressure on the processor sub-system.)
If you scale these statistics out to a larger amount of data, and consider realistic pressure on the processor from other processes, the result is that the if you are only returning a small number of columns, from an XML document, nodes will usually perform better. However, the more columns you are returning, the more Value method statements you will be performing, and the more pressure you will put on the processor, so as the number of columns returned increases, you will see OPENXML start to catch up, and eventually overtake the Nodes method on execution time. The more existing pressure there is on the processor, the faster this will happen.


Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom

Shredding XML with OPENXML & NODES - PART II

...In Part I, I discussed how to shred XML data by using the OPENXML method. So how would we perform this same operation by using the Nodes XQuery method? Well, firstly, we would once again need to create an XML variable, and assign our document to it...


DECLARE @XML_Document XML

SET @XML_Document = N'<SalesOrder>
  <soh SalesOrderID="43667" OrderDate="2001-07-01T00:00:00" CustomerID="646" TotalDue="8095.7863">
    <sod ProductID="710" OrderQty="3" UnitPrice="5.7000" />
    <sod ProductID="773" OrderQty="1" UnitPrice="2039.9940" />
    <sod ProductID="778" OrderQty="1" UnitPrice="2024.9940" />
    <sod ProductID="775" OrderQty="1" UnitPrice="2024.9940" />
  </soh>
  <soh SalesOrderID="44287" OrderDate="2001-10-01T00:00:00" CustomerID="646" TotalDue="21601.0565">
    <sod ProductID="773" OrderQty="2" UnitPrice="2039.9940" />
    <sod ProductID="777" OrderQty="2" UnitPrice="2024.9940" />
    <sod ProductID="776" OrderQty="1" UnitPrice="2024.9940" />
    <sod ProductID="709" OrderQty="3" UnitPrice="5.7000" />
    <sod ProductID="771" OrderQty="3" UnitPrice="2039.9940" />
  </soh>
  <soh SalesOrderID="45045" OrderDate="2002-01-01T00:00:00" CustomerID="646" TotalDue="18909.0911">
    <sod ProductID="709" OrderQty="2" UnitPrice="5.7000" />
    <sod ProductID="778" OrderQty="1" UnitPrice="2024.9940" />
    <sod ProductID="771" OrderQty="1" UnitPrice="2039.9940" />
    <sod ProductID="774" OrderQty="1" UnitPrice="2039.9940" />
    <sod ProductID="773" OrderQty="3" UnitPrice="2039.9940" />
    <sod ProductID="776" OrderQty="1" UnitPrice="2024.9940" />
  </soh>
  <soh SalesOrderID="45785" OrderDate="2002-04-01T00:00:00" CustomerID="646" TotalDue="54042.8077">
    <sod ProductID="715" OrderQty="2" UnitPrice="28.8404" />
    <sod ProductID="774" OrderQty="4" UnitPrice="2039.9940" />
    <sod ProductID="771" OrderQty="3" UnitPrice="2039.9940" />
    <sod ProductID="773" OrderQty="2" UnitPrice="2039.9940" />
    <sod ProductID="772" OrderQty="2" UnitPrice="2039.9940" />
    <sod ProductID="709" OrderQty="2" UnitPrice="5.7000" />
    <sod ProductID="710" OrderQty="2" UnitPrice="5.7000" />
    <sod ProductID="712" OrderQty="4" UnitPrice="5.1865" />
    <sod ProductID="775" OrderQty="2" UnitPrice="2024.9940" />
    <sod ProductID="776" OrderQty="3" UnitPrice="2024.9940" />
    <sod ProductID="778" OrderQty="2" UnitPrice="2024.9940" />
    <sod ProductID="777" OrderQty="2" UnitPrice="2024.9940" />
  </soh>
  <soh SalesOrderID="61206" OrderDate="2004-01-01T00:00:00" CustomerID="646" TotalDue="50.2703">
    <sod ProductID="869" OrderQty="1" UnitPrice="41.9940" />
  </soh>
</SalesOrder>'

...We will now combine the Value method, with the Nodes method, to retrieve a relation result set...

SELECT temp_column.value('../@SalesOrderID', 'int') AS 'SalesOrderID',
temp_column.value('../@OrderDate', 'datetime') AS 'OrderDate',
temp_column.value('../@CustomerID', 'int') AS 'CustomerID',
temp_column.value('../@TotalDue', 'decimal') AS 'TotalDue',
temp_column.value('@ProductID', 'int') AS 'ProductID',
temp_column.value('@OrderQty', 'int') AS 'OrderQty',
temp_column.value('@UnitPrice', 'decimal') AS 'UnitPrice'
FROM @xml_document.nodes('/SalesOrder/soh/sod') temp_table(temp_column)

...This statement will produce an identical result set to the OPENXML statement we discussed earlier...



...So how is this statement working? Well, at first glance, the syntax can be quite confusing. The trick to understanding it, is to remember that SQL Server does not actually process a statement in the order that you write it. The actual order of processing here is as follows...
Create a temporary table called temp_table, containing one column called temp_column.
Populate temp_column with the results of the nodes statement, which is a parsed version of our XML document.
Repeatedly run the value method against the temporary column, once for each node you wish to extract, mapping each node to a relational column and data type.
It would also be possible to use this process to what I like to call ‘semi-shred’ the data. By that, I mean to split the document into a row set, but with each row containing an XML document. In this example, there would be five rows, each containing one sales order.
To do this, the syntax would be similar, but we will replace the value method with the query method, which returns an XML fragment, as opposed to a scalar value...


SELECT temp_column.query('.')
FROM @xml_document.nodes('/SalesOrder/soh') temp_table(temp_column)

...The row set returned will look like this... 


...The first XML fragment in the row set would look like this...

<soh SalesOrderID="43667" OrderDate="2001-07-01T00:00:00" CustomerID="646" TotalDue="8095.7863">
  <sod ProductID="710" OrderQty="3" UnitPrice="5.7000" />
  <sod ProductID="773" OrderQty="1" UnitPrice="2039.9940" />
  <sod ProductID="778" OrderQty="1" UnitPrice="2024.9940" />
  <sod ProductID="775" OrderQty="1" UnitPrice="2024.9940" />
</soh>

...So now we know how to shred XML using both the OPENXML function and the nodes method, the question remains, when should we use each option? A good question, and one that I will answer in Part III...

Shredding XML with OPENXML & NODES - PART I

In SQL Server 2005 & 2008, there are two ways of shredding XML into relational data. The first is the ‘traditional method’ of using OPENXML, but there is also the more ‘contemporary’ method of using the Nodes XQuery method, but which should you use and when?
Well, before we begin to tackle that question, lets look at how to implement each technique. But before we can shred XML, we will need an XML document, so the first thing I will do, is generate a very simple XML document with the following statement...

SELECT soh.SalesOrderID, soh.OrderDate, soh.CustomerID, soh.TotalDue, sod.ProductID, sod.OrderQty, sod.UnitPrice
FROM Sales.SalesOrderHeader soh
INNER JOIN Sales.SalesOrderDetail sod
ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.CustomerID = 646
FOR XML AUTO, ROOT('SalesOrder')
...So now, to shred this data using the OPENXML function. The first thing that we will need to do is to declare two variables. The first will hold this XML document, and the second will be an Integer, that will be used to point to a parsed version of the document that will be stored in memory...

DECLARE @XML_Document XML
DECLARE @Handle INT
SET @XML_Document = N'<SalesOrder>
  <soh SalesOrderID="43667" OrderDate="2001-07-01T00:00:00" CustomerID="646" TotalDue="8095.7863">
    <sod ProductID="710" OrderQty="3" UnitPrice="5.7000" />
    <sod ProductID="773" OrderQty="1" UnitPrice="2039.9940" />
    <sod ProductID="778" OrderQty="1" UnitPrice="2024.9940" />
    <sod ProductID="775" OrderQty="1" UnitPrice="2024.9940" />
  </soh>
  <soh SalesOrderID="44287" OrderDate="2001-10-01T00:00:00" CustomerID="646" TotalDue="21601.0565">
    <sod ProductID="773" OrderQty="2" UnitPrice="2039.9940" />
    <sod ProductID="777" OrderQty="2" UnitPrice="2024.9940" />
    <sod ProductID="776" OrderQty="1" UnitPrice="2024.9940" />
    <sod ProductID="709" OrderQty="3" UnitPrice="5.7000" />
    <sod ProductID="771" OrderQty="3" UnitPrice="2039.9940" />
  </soh>
  <soh SalesOrderID="45045" OrderDate="2002-01-01T00:00:00" CustomerID="646" TotalDue="18909.0911">
    <sod ProductID="709" OrderQty="2" UnitPrice="5.7000" />
    <sod ProductID="778" OrderQty="1" UnitPrice="2024.9940" />
    <sod ProductID="771" OrderQty="1" UnitPrice="2039.9940" />
    <sod ProductID="774" OrderQty="1" UnitPrice="2039.9940" />
    <sod ProductID="773" OrderQty="3" UnitPrice="2039.9940" />
    <sod ProductID="776" OrderQty="1" UnitPrice="2024.9940" />
  </soh>
  <soh SalesOrderID="45785" OrderDate="2002-04-01T00:00:00" CustomerID="646" TotalDue="54042.8077">
    <sod ProductID="715" OrderQty="2" UnitPrice="28.8404" />
    <sod ProductID="774" OrderQty="4" UnitPrice="2039.9940" />
    <sod ProductID="771" OrderQty="3" UnitPrice="2039.9940" />
    <sod ProductID="773" OrderQty="2" UnitPrice="2039.9940" />
    <sod ProductID="772" OrderQty="2" UnitPrice="2039.9940" />
    <sod ProductID="709" OrderQty="2" UnitPrice="5.7000" />
    <sod ProductID="710" OrderQty="2" UnitPrice="5.7000" />
    <sod ProductID="712" OrderQty="4" UnitPrice="5.1865" />
    <sod ProductID="775" OrderQty="2" UnitPrice="2024.9940" />
    <sod ProductID="776" OrderQty="3" UnitPrice="2024.9940" />
    <sod ProductID="778" OrderQty="2" UnitPrice="2024.9940" />
    <sod ProductID="777" OrderQty="2" UnitPrice="2024.9940" />
  </soh>
  <soh SalesOrderID="61206" OrderDate="2004-01-01T00:00:00" CustomerID="646" TotalDue="50.2703">
    <sod ProductID="869" OrderQty="1" UnitPrice="41.9940" />
  </soh>
</SalesOrder>'
...The next step, is to create the parsed version of the document in memory. To do this, we pass the two variables that we have just created into the sp_xml_preparedocument procedure...
 
EXEC sp_xml_preparedocument @handle OUTPUT, @xml_document
You will notice that in this statement, I have specified the OUTPUT keyword after our @handle parameter. This is because the pointer to the parse tree is being assigned to this variable, and from now on, we will no longer reference our original XML document, we will only ever reference the pointer.
So now, we will use the OPENXML function itself, to produce our relational result set...

SELECT *
FROM
OPENXML ( @handle, '/SalesOrder/soh/sod', 1)
WITH ( SalesOrderID     int         '../@SalesOrderID',
       OrderDate        datetime    '../@OrderDate',
       CustomerID       int         '../@CustomerID',
       TotalDue         decimal     '../@TotalDue',
       ProductID        int         '@ProductID',
       OrderQty         int         '@OrderQty',
       UnitPrice        decimal     '@UnitPrice')
...Here, we are specifying that we want to select all of the relational columns that we are mapping to the nodes within the XML document.
The three variables that we are passing into the OPENXML statement are the handle, the row pattern and the mapping flag. The row pattern specifies the lowest node level of the XML document that we will be extracting data from, and the mapping flag specifies how nodes will be mapped to relational columns. This parameter must always be specified, but in this example has no effect, because we are explicitly mapping all nodes. This parameter comes into its own, if we are using auto mapping, which I will discuss in a later post.
In the WITH clause, we are specifying the nodes that we wish to return, and mapping them to relational columns and data types. The @ symbol, is specifying that the node is an attribute. If the node was an element, we would omit this symbol. Where you see “../”, we are moving up one level of the node hierarchy. If we wanted to move up two levels, we would state “../../”, three levels would be “../../../”, etc.
For example, UnitPrice is an attribute at the lowest level that we specified in the row pattern, so there is no need to move up the hierarchy to find the node. Hence, there is no “../”. However, the SalesOrderID attribute is at the next level of the node hierarchy, so we have specified “../”.
The last thing that we need to do, is to remove the handle to the parsed document. As OPENXML is an expensive operation, this is VERY important. Think of it like de-allocating a cursor! We will perform this clean-up with the following statement...
 
EXEC sp_xml_removedocument @handle
...The memory space can now be reallocated and reused.
This code should give the following result set...

In Part II, I will discuss how to produce the same results using the nodes XQuery method...