Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

My technology blog on SQL Server, TSQL, XML, FILESTREAM and other areas of SQL Server.
Browse by Tags · View All
XML 112
TSQL 69
XQuery 69
XQuery Functions 67
XQuery Training 65
XQuery in TSQL 64
XQuery Tutorial 63
SQL Server XQuery 63
XQuery-Labs 57
BRH 38

Archive · View All
September 2008 32
August 2008 30
July 2008 21
August 2009 19
June 2009 19
May 2010 18
January 2009 15
January 2010 14
October 2008 14
June 2008 13

XQuery Lab 58 - SELECT * FROM XML

May 30 2010 11:49PM by Jacob Sebastian   

Most people find it very difficult to deal with XML documents in TSQL as there is no way to run a ‘blind’ SELECT * query on an XML document to get a quick view of the content stored in it. A “select TOP N *” query can quickly give you a few records from the table which will give you an idea about the structure of the table and the type of values stored in the columns.  One of the common queries that I run on a table that I am not familiar with is

SELECT TOP 1 * FROM tablename

This query will give me one record that I can review and understand the structure of the table. However, it is really hard to do something similar for an XML document. The “*” operator does not work for XML and hence I can write a query on the XML document only if I know the structure of the XML.

To make this easier, I have come up with a function that can give you a “SELECT * FROM XML” kind of functionality. You can pass an XML document to the function and it will return a tabular representation of the XML data. Here is an example that shows how you can use this function.

declare @x xml
select @x = '
<employees>
    <emp name="jacob"/>
    <emp name="steve">
        <phone>123</phone>
    </emp>
</employees>
'
SELECT * FROM dbo.XMLTable(@x) 

/*
NodeName  NodeType  XPath                        TreeView      Value XmlData      
--------- --------- ---------------------------- ------------- ----- -------------
employees Element   employees[1]                 employees     NULL  <employees>..
emp       Element   employees[1]/emp[1]              emp       NULL  <emp name="..
name      Attribute employees[1]/emp[1]/@name            @name jacob NULL
emp       Element   employees[1]/emp[2]              emp       NULL  <emp name="..
name      Attribute employees[1]/emp[2]/@name            @name steve NULL
phone     Element   employees[1]/emp[2]/phone[1]         phone 123   <phone>123<..
*/

The ‘XPath’ column may be very helpful as it shows the XPath expression that you can use to retrieve a specific value from the XML document. For example, to retrieve the phone number, you can copy the XPath expression from the above result and directly put it in a query such as:

SELECT @x.value('employees[1]/emp[2]/phone[1]','VARCHAR(20)') AS Phone

/*
Phone
--------------------
123
*/

Here is the complete listing of the function

/*----------------------------------------------------------------------------- 
  Date       : 1 May 2010 
  SQL Version: SQL Server 2005/2008 
  Author     : Jacob Sebastian 
  Email      : jacob@beyondrelational.com 
  Twitter    : @jacobsebastian  
  Blog       : http://beyondrelational.com/blogs/jacob 
  Website    : http://beyondrelational.com

  Summary: 
  This script returns a tabular representation of an XML document

  Modification History:
  Jacob Sebastian - 1 May 2010
		Created the first version
  Jacob Sebatian - 18 June 2010
		Fixed a bug in the XPath Expressiong generated
  Jacob Sebastian - 20 June 2010
		Added new column - ParentName
		Updated the 'treeview' column to show lines
		Added new column - 'Position'
		Added New Column - 'ParentPosition'
  Jacob Sebastian - 23 June 2010		
		Made the function UNICODE compatibile. (Thanks Peso)
  Jacob Sebastian - 30 June 2010		
		Corrected the casing of a few columns to make the function
		work on case sensitive SQL Server installations. 
		(Thanks Rhodri Evans)               
		
  Notes:
  If you find this script useful, let us know by writing a comment at
  http://beyondrelational.com/blogs/jacob/archive/2010/05/30/select-from-xml.aspx
	
  Disclaimer:  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A 
  PARTICULAR PURPOSE. 
-----------------------------------------------------------------------------*/ 
/* 
SELECT * FROM dbo.XMLTable(' 
<employees> 
    <emp name="jacob"/> 
    <emp name="steve"> 
        <phone>123</phone> 
    </emp> 
</employees> 
') 
*/ 
CREATE FUNCTION [dbo].[XMLTable]( 
    @x XML 
) 
RETURNS TABLE 
AS RETURN 
/*---------------------------------------------------------------------- 
This INLINE TVF uses a recursive CTE that processes each element and 
attribute of the XML document passed in. 
----------------------------------------------------------------------*/ 
WITH cte AS ( 
    /*------------------------------------------------------------------ 
    Anchor part of the recursive query. Retrieves the root element 
    of the XML document 
    ------------------------------------------------------------------*/ 
    SELECT 
        1 AS lvl, 
        x.value('local-name(.)','NVARCHAR(MAX)') AS Name, 
        CAST(NULL AS NVARCHAR(MAX)) AS ParentName,
        CAST(1 AS INT) AS ParentPosition,
        CAST(N'Element' AS NVARCHAR(20)) AS NodeType, 
        x.value('local-name(.)','NVARCHAR(MAX)') AS FullPath, 
        x.value('local-name(.)','NVARCHAR(MAX)') 
            + N'[' 
            + CAST(ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS NVARCHAR) 
            + N']' AS XPath, 
        ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS Position,
        x.value('local-name(.)','NVARCHAR(MAX)') AS Tree, 
        x.value('text()[1]','NVARCHAR(MAX)') AS Value, 
        x.query('.') AS this,        
        x.query('*') AS t, 
        CAST(CAST(1 AS VARBINARY(4)) AS VARBINARY(MAX)) AS Sort, 
        CAST(1 AS INT) AS ID 
    FROM @x.nodes('/*') a(x) 
    UNION ALL 
    /*------------------------------------------------------------------ 
    Start recursion. Retrieve each child element of the parent node 
    ------------------------------------------------------------------*/ 
    SELECT 
        p.lvl + 1 AS lvl, 
        c.value('local-name(.)','NVARCHAR(MAX)') AS Name, 
        CAST(p.Name AS NVARCHAR(MAX)) AS ParentName,
        CAST(p.Position AS INT) AS ParentPosition,
        CAST(N'Element' AS NVARCHAR(20)) AS NodeType, 
        CAST( 
            p.FullPath 
            + N'/' 
            + c.value('local-name(.)','NVARCHAR(MAX)') AS NVARCHAR(MAX) 
        ) AS FullPath, 
        CAST( 
            p.XPath 
            + N'/' 
            + c.value('local-name(.)','NVARCHAR(MAX)') 
            + N'[' 
            + CAST(ROW_NUMBER() OVER(
				PARTITION BY c.value('local-name(.)','NVARCHAR(MAX)')
				ORDER BY (SELECT 1)) AS NVARCHAR	) 
            + N']' AS NVARCHAR(MAX) 
        ) AS XPath, 
        ROW_NUMBER() OVER(
				PARTITION BY c.value('local-name(.)','NVARCHAR(MAX)')
				ORDER BY (SELECT 1)) AS Position,
        CAST( 
            SPACE(2 * p.lvl - 1) + N'|' + REPLICATE(N'-', 1)
            + c.value('local-name(.)','NVARCHAR(MAX)') AS NVARCHAR(MAX) 
        ) AS Tree, 
        CAST( c.value('text()[1]','NVARCHAR(MAX)') AS NVARCHAR(MAX) ) AS Value, 
        c.query('.') AS this,        
        c.query('*') AS t, 
        CAST( 
            p.Sort 
            + CAST( (lvl + 1) * 1024 
            + (ROW_NUMBER() OVER(ORDER BY (SELECT 1)) * 2) AS VARBINARY(4) 
        ) AS VARBINARY(MAX) ) AS Sort, 
        CAST( 
            (lvl + 1) * 1024 
            + (ROW_NUMBER() OVER(ORDER BY (SELECT 1)) * 2) AS INT 
        ) 
    FROM cte p 
    CROSS APPLY p.t.nodes('*') b(c)        
), cte2 AS ( 
    SELECT 
        lvl AS Depth, 
        Name AS NodeName, 
        ParentName,
        ParentPosition,
        NodeType, 
        FullPath, 
        XPath, 
        Position,
        Tree AS TreeView, 
        Value, 
        this AS XMLData, 
        Sort, ID 
    FROM cte 
    UNION ALL 
    /*------------------------------------------------------------------ 
    Attributes do not need recursive calls. So add the attributes 
    to the query output at the end. 
    ------------------------------------------------------------------*/ 
    SELECT 
        p.lvl, 
        x.value('local-name(.)','NVARCHAR(MAX)'), 
        p.Name,
        p.Position,
        CAST(N'Attribute' AS NVARCHAR(20)), 
        p.FullPath + N'/@' + x.value('local-name(.)','NVARCHAR(MAX)'), 
        p.XPath + N'/@' + x.value('local-name(.)','NVARCHAR(MAX)'), 
        1,
        SPACE(2 * p.lvl - 1) + N'|' + REPLICATE('-', 1) 
			+ N'@' + x.value('local-name(.)','NVARCHAR(MAX)'), 
        x.value('.','NVARCHAR(MAX)'), 
        NULL, 
        p.Sort, 
        p.ID + 1 
    FROM cte p 
    CROSS APPLY this.nodes('/*/@*') a(x) 
) 
SELECT 
    ROW_NUMBER() OVER(ORDER BY Sort, ID) AS ID, 
    ParentName, ParentPosition,Depth, NodeName, Position,  
    NodeType, FullPath, XPath, TreeView, Value, XMLData
FROM cte2

Next Steps

  1. The function currently does not support namespaces. The next version will add support for namespaces
  2. Let me know your comments and feedback on this function.

Tags: XQuery-Labs, XML, XQuery, BRH, XQuery Functions, XML Toolkit, XML Library, xquery labs, SQL Server XQuery, XQuery in TSQL, XQuery Training, XQuery Tutorial,


Jacob Sebastian
1 · 100% · 32220
3
 
0
Lifesaver
 
0
Refreshed
 
 
0
Incorrect



Submit

11  Comments  

  • Brilliant! Looking forward to the version supporting namespaces

    commented on Jun 10 2010 5:01AM
    Anders Bendix Nielsen
    678 · 0% · 49
  • Hi, I love your code. It helped me a great lot. I have one question though. It seems as if the position column is not returning anything by the number 1 or 2 for nodename Row. I would like the rows to be sorted based on the columns that are mapped in the database. meaning as the xml comes in and not sorted by the fullpath characters. Is that possible to do? can you help me out? thanks, Joel

    commented on Jul 22 2010 4:01PM
    jkandy
    2725 · 0% · 4
  • Love the code, really helped me. I managed to fix a small bug that you had with the position column, however, i am unable to make the table sort itself according to the order that comes in from the sql. It sorts by the alphabet ascending of the column name. Can you help?

    commented on Jul 22 2010 4:20PM
    jkandy
    2725 · 0% · 4
  • What was the bug? Can you explain it with an example?

    commented on Jul 23 2010 2:14AM
    Jacob Sebastian
    1 · 100% · 32220
  • Hi Jacob,

    This is really a good exercise. I wish we could have an xml method to return the same edge table as when we use OPENXML without "WITH", or the node table produced by the iterator "Table Valued Function - XML Reader " when you use the nodes method of the xml data type (http://msdn.microsoft.com/en-us/library/ms345121(SQL.90).aspx), which I believe is the same information used by a primary xml index.

    DECLARE @idoc int
    DECLARE @x XML   
    
    SET @x = ' 
    <books xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> 
        <book id="101">
            <title>my book</title> 
            <author>Myself</author> 
        </book> 
        <book id="202">
            <title>your book</title> 
            <author>you</author>
        </book>
        <book id="303">
            <title>XQuery & XML Schema</title> 
            <author xsi:nil="true" />
        </book>    
    </books>';
    
    EXEC sp_xml_preparedocument @idoc OUTPUT, @x;
    
    SELECT
        id,
        parentid,
        nodetype,
        localname,
        prefix,
        namespaceuri,
        datatype,
        prev,
        [text]
    FROM
        OPENXML(@idoc, '.') AS T;
    
    EXEC sp_xml_removedocument @idoc;
    GO
    

    Cheers, AMB

    commented on Aug 12 2010 1:40PM
    Alejandro Mesa
    268 · 1% · 164
  • Hi various people have asked about this ... FIX - To obtain original element order change ... line 127 FROM cte p ... to ... FROM (select *, row_number() over (order by (select(1))) as ItemIndex from cte) p

    change ... lines 143 and 164 FROM cte --> ,row_number() over (order by (select(1))) as ItemIndex FROM cte

    change ... line 168 ROW_NUMBER() OVER(ORDER BY Sort, ID) AS ID, --> ItemIndex,

    Hopefully you can apply this to your superb script to make it available to everyone. PS. I have not removed the other reference to ID, but you could probably remove them all.

    Regards, Geoff.

    commented on Jan 10 2013 4:07AM
    salford
    3015 · 0% · 2
  • Hello,

    First of all thanks for the function! It is great!

    Now I'm trying to overcome a challenge of preserving the original order of nodes. The solution described above (by salford) seems not working for me.

    Please advise.

    Thank you in advance!

    commented on Feb 20 2013 11:13AM
    iulyanov
    3015 · 0% · 2
  • Hi,

    Great function. Exactly what I was looking for - really really helped me out.

    Like the previous two posts I, too, am looking to get the results in original node-order, not sorted alphabetically. The solution described above (by salford) only works for me when there's no root node

    Using salford's example...

    This comes out sorted alphabetically:

    <root>
        <bbb>something</bbb>
        <aaa>something</aaa>
      </root>
      

    Result:

    NodeName | Value
      ---------------------
      aaa      | something
      bbb      | something
      

    But this (removing the root node) makes it come out in the original order:

      <bbb>something</bbb>
        <aaa>something</aaa>
      

    Result:

    NodeName | Value
      ---------------------
      bbb      | something
      aaa      | something
      

    Can anyone help so the first example comes out sorted in the original order?

    commented on Apr 10 2013 3:21AM
    CellsReinvent
    3015 · 0% · 2
  • Excellent code and most helpful indeed - thanks! I know a couple of people have asked whether it is possible to return the results in original node order; did anyone ever figure out how to do this?

    commented on Sep 9 2013 1:12AM
    meredigr
    3015 · 0% · 2
  • Should be possible because XML preserves the physical order of elements. I did not get time to review the code and check it, but I believe it should be possible to do with minor modifications.

    commented on Sep 10 2013 8:40PM
    Jacob Sebastian
    1 · 100% · 32220
  • Thanks for this wonderful query, but can I ask one question?

    I want to store the extracted data in a hierarchical format - with each line having the ID, node name, value and the id of it's parent record

    For example:

    declare @input_xml xml
    set @input_xml = '<ArrayOfAvailableRoute>
    <AvailableRoute>
    <AvailableFlights>
    <AvailableFlight>
    <DepartureLocation>
     <AirportId>LGW</AirportId>
     </DepartureLocation>
    <ArrivalLocation>
     <AirportId>PFO</AirportId>
     </ArrivalLocation>
     <DepartureDateTime>2013-08-26T13:10:00</DepartureDateTime>
     <ArrivalDateTime>2013-08-26T19:40:00</ArrivalDateTime>
     </AvailableFlight>
     </AvailableFlights>
     </AvailableRoute>
    <AvailableRoute>
    <AvailableFlights>
    <AvailableFlight>
    <DepartureLocation>
     <AirportId>PFO</AirportId>
     </DepartureLocation>
    <ArrivalLocation>
     <AirportId>LGW</AirportId>
     </ArrivalLocation>
     <DepartureDateTime>2013-09-02T20:40:00</DepartureDateTime>
     <ArrivalDateTime>2013-09-02T23:35:00</ArrivalDateTime>
     </AvailableFlight>
     </AvailableFlights>
     </AvailableRoute>
     </ArrayOfAvailableRoute>'
    
    select id, Nodename, Value, parentposition from dbo.XMLTable(@input_xml)
    

    This gives this output:

    ID  NodeName	Value	ParentPosition
    1   ArrayOfAvailableRoute	NULL	1
    2   AvailableRoute	NULL	1
    3   AvailableFlights	NULL	1
    4   AvailableFlight	NULL	1
    5   ArrivalDateTime	2013-08-26T19:40:00	1
    6   ArrivalLocation	NULL	1
    7   AirportId	PFO	1
    8   DepartureDateTime	2013-08-26T13:10:00	1
    9   DepartureLocation	NULL	1
    10  AirportId	LGW	1
    11  AvailableRoute	NULL	1
    12  ArrivalDateTime	0001-01-01T00:00:00	2
    13  AvailableFlights	NULL	2
    14  AvailableFlight	NULL	1
    15  ArrivalDateTime	2013-09-02T23:35:00	1
    16  ArrivalLocation	NULL	1
    17  AirportId	LGW	1
    18  DepartureDateTime	2013-09-02T20:40:00	1
    19  DepartureLocation	NULL	1
    20  AirportId	PFO	1
    21  DepartureDateTime	0001-01-01T00:00:00	2
    

    What I would like to get back is:

    ID  NodeName	Value	ParentID
    1   ArrayOfAvailableRoute	NULL	0
    2   AvailableRoute	NULL	1
    3   AvailableFlights	NULL	2
    4   AvailableFlight	NULL	3
    5   ArrivalDateTime	2013-08-26T19:40:00	4
    6   ArrivalLocation	NULL	4
    7   AirportId	PFO	6
    8   DepartureDateTime	2013-08-26T13:10:00	4
    9   DepartureLocation	NULL	4
    10  AirportId	LGW	9
    11  AvailableRoute	NULL	1
    12  AvailableFlights	NULL	11
    13  AvailableFlight	NULL	12
    14  ArrivalDateTime	2013-09-02T23:35:00	13
    15  ArrivalLocation	NULL	13
    16  AirportId	LGW	15
    17  DepartureDateTime	2013-09-02T20:40:00	13
    18  DepartureLocation	NULL	13
    19  AirportId	PFO	18
    
    commented on Oct 9 2013 10:01AM
    Sequin
    2187 · 0% · 6

Your Comment


Sign Up or Login to post a comment.

"XQuery Lab 58 - SELECT * FROM XML" rated 5 out of 5 by 3 readers
XQuery Lab 58 - SELECT * FROM XML , 5.0 out of 5 based on 3 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]