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 1 - Transforming rows to columns

Jun 26 2008 11:51PM by Jacob Sebastian   

This is the first of a series of XQuery posts I plan to write under the ‘XQuery Labs’ series. The purpose of this series is to demonstrate the usages of XML Data type methods and XQuery functions.

Input

I have an XML document which looks like the following.

<QUOTE configID="arbu05_0X4fe77d8454141612">
<CSTICS>
<ORDER_CFGS_VALUE>
<Field Name="CONFIG_ID" Value="arbu05_0X4fe77d8454141612" />
<Field Name="INST_ID" Value="2" />
<Field Name="CHARC" Value="A7J_ID_CONFIG" />
<Field Name="VALUE" Value="0" />
</ORDER_CFGS_VALUE>
<ORDER_CFGS_VALUE>
<Field Name="CONFIG_ID" Value="arbu05_0X4fe77d8454141612" />
<Field Name="INST_ID" Value="2" />
<Field Name="CHARC" Value="A7J_ID_ELECTRICAL" />
<Field Name="VALUE" Value="0" />
</ORDER_CFGS_VALUE>
</CSTICS>
</QUOTE>

Look at the child nodes of ORDER_CFGS_VALUE. Each ORDER_CFGS_VALUE element has 4 child nodes named field. We need to write a query that transforms those nodes into columns.

Expected output

The output should look like the following.

CONFIG_ID            INST_ID     CHARC                VALUE
-------------------- ----------- -------------------- -----------
arbu05_0X4fe77d84541 2           A7J_ID_CONFIG        0
arbu05_0X4fe77d84541 2           A7J_ID_ELECTRICAL    0

The query should return only two rows; one row for each ORDER_CFGS_VALUE. The children of ORDER_CFGS_VALUE having name CONFIG_ID, INST_ID, CHARC and VALUE should be transformed as columns.

The Query

DECLARE @x XML
SELECT @x = '
<QUOTE configID="arbu05_0X4fe77d8454141612">
<CSTICS>
<ORDER_CFGS_VALUE>
<Field Name="CONFIG_ID" Value="arbu05_0X4fe77d8454141612" />
<Field Name="INST_ID" Value="2" />
<Field Name="CHARC" Value="A7J_ID_CONFIG" />
<Field Name="VALUE" Value="0" />
</ORDER_CFGS_VALUE>
<ORDER_CFGS_VALUE>
<Field Name="CONFIG_ID" Value="arbu05_0X4fe77d8454141612" />
<Field Name="INST_ID" Value="2" />
<Field Name="CHARC" Value="A7J_ID_ELECTRICAL" />
<Field Name="VALUE" Value="0" />
</ORDER_CFGS_VALUE>
</CSTICS>
</QUOTE>'


SELECT
x.value('(Field[@Name="CONFIG_ID"]/@Value)[1]','VARCHAR(20)') AS CONFIG_ID,
x.value('(Field[@Name="INST_ID"]/@Value)[1]','INT') AS INST_ID,
x.value('(Field[@Name="CHARC"]/@Value)[1]','VARCHAR(20)') AS CHARC,
x.value('(Field[@Name="VALUE"]/@Value)[1]','INT') AS VALUE
FROM @x.nodes('/QUOTE/CSTICS/ORDER_CFGS_VALUE') d(x)

/*
CONFIG_ID INST_ID CHARC VALUE
-------------------- ----------- -------------------- -----------
arbu05_0X4fe77d84541 2 A7J_ID_CONFIG 0
arbu05_0X4fe77d84541 2 A7J_ID_ELECTRICAL 0
*/

Note the way filters are applied on the value of attribute "Name". The expression "Field[@Name="CONFIG_ID"]" will take you to the XML element having "CONFIG_ID" as the value of the "Name" attribute. After accessing this element, the value of the "value" attribute is retrieved with the expression "Field[@Name="CONFIG_ID"]/@Value"

Comments/Questions are welcome!

XQuery Labs - A Collection of XQuery Sample Scripts

Tags: XQuery-Labs, XML, XQuery, XQuery Functions, SQL Server XQuery, XQuery in TSQL, XQuery Training, XQuery Tutorial,


Jacob Sebastian
1 · 100% · 32235
3
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

4  Comments  

  • I have tried in vain to adapt your advice to this XML:

    1 1 25 2 25 3 50

    I am trying to change the XML to a resultset like this:

    sourceposition fundposition percent 1 1 25 1 2 25 1 3 50

    If there are multiple source positions, I need those to display as additional rows, as well. This is the query that I tried, and I just get NULL values.

    SELECT x.value('(Field[@Name="sourceposition"]/@Value)[1]','VARCHAR(20)') AS sourceposition,
    x.value('(Field[@Name="fundposition"]/@Value)[1]','VARCHAR(20)') AS fundposition,
    x.value('(Field[@Name="percent"]/@Value)[1]','VARCHAR(20)') AS [percent]

    FROM @x.nodes('/accountorder/source') d(x)

    Thanks in advance for your help!

    commented on Dec 1 2009 6:44PM
    mmorrow
    2760 · 0% · 4
  • Try this query:

    SELECT p.value('.','INT') AS SourcePosition, v.value('fundposition[1]','INT') AS fundposition, v.value('percent[1]','INT') AS FundPercent FROM @x.nodes('/accountorder/source') d(x) CROSS APPLY x.nodes('sourceposition') s(p) CROSS APPLY x.nodes('fund') AS f(v)

    commented on Dec 1 2009 9:49PM
    Jacob Sebastian
    1 · 100% · 32235
  • That worked awesome!!! If I wanted to incorporate that into a query of a table called psr with a field called psr_xml, how would I do that?

    With what you provided, I will be able to write a stored proc to pull a specified record, declare the xml variable, and return this result. However, I would like to parse XML from multiple records, as well.

    Thanks again!

    commented on Dec 2 2009 1:05PM
    mmorrow
    2760 · 0% · 4
  • does this help?

    declare @t table (id int identity, data xml) insert into @t (data) select ' 1 1 25 2 25 '

    insert into @t (data) select ' 1 1 25 3 50 '

    SELECT p.value('.','INT') AS SourcePosition, v.value('fundposition[1]','INT') AS fundposition, v.value('percent[1]','INT') AS FundPercent FROM @t CROSS APPLY data.nodes('/accountorder/source') d(x) CROSS APPLY x.nodes('sourceposition') s(p) CROSS APPLY x.nodes('fund') AS f(v)

    commented on Dec 14 2009 5:38AM
    Jacob Sebastian
    1 · 100% · 32235

Your Comment


Sign Up or Login to post a comment.

"XQuery Lab 1 - Transforming rows to columns" rated 5 out of 5 by 3 readers
XQuery Lab 1 - Transforming rows to columns , 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]