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

Another XML Shaping Example - using FOR XML PATH and EXPLICIT

Dec 14 2007 9:15AM by Jacob Sebastian   

In many of the previous posts, we discussed about controlling the structure of the XML being generated with FOR XML. SQL Server 2005 introduced the PATH operator which provides a great extend of control over the shaping of the XML being generated. With PATH, we could achieve most of the XML shaping requirements previously possible only with FOR XML EXPLICIT. In my XML Workshop Series at www.sqlservercentral.com, I have discussed many of the XML shaping requirements and different ways to meet those requirements.

I just helped some one to solve another XML shaping problem in the MSDN forums and thought of sharing it here, because the problem seems to be common. Position of elements is significant in XML. Hence, some times we need to control the position of elements being generated. We might need to have a certain element placed before another. Most of the times when we write a FOR XML EXPLICIT query, we might need to have elements placed in a given order. I have discussed it in the FOR XML EXPLICIT tutorial given here.

Let us look into the problem and the solution.

Source data

/*
id NameA NameB NameC
----------- ---------- ---------- ----------
1 Value A Value B Value C
*/

Current Query

SELECT 
1 AS Tag,
NULL AS Parent,
ID AS [Tab!1!ID],
NameA AS [Tab!1!NameA!Element],
NULL AS [Tab2!2!NameB!Element],
NameC AS [Tab!1!NameC!Element]
FROM tbl
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
ID,
NULL AS [Tab!1!NameA!Element],
NameB AS [Tab!1!NameB!Element],
NULL AS [Tab!1!NameC!Element]
FROM tbl
FOR XML EXPLICIT

Current XML Result

<Tab ID="1">
<NameA>Value A</NameA>
<NameC>Value C</NameC>
<Tab2>
<NameB>Value B</NameB>
</Tab2>
</Tab>

Expected XML Result

<Tab ID="1">
<NameA>Value A</NameA>
<Tab2>
<NameB>Value B</NameB>
</Tab2>
<NameC>Value C</NameC>
</Tab>

Corrected Solution

We have two options here. We could either go with FOR XML PATH or FOR XML EXPLICIT (FOR XML PATH is available only on SQL server 2005 and above). Here is the query that uses FOR XML EXPLICIT.

DECLARE @tmp TABLE (
id Int,
NameA VarChar(10),
NameB VarChar(10),
NameC VarChar(10)
)

INSERT INTO @tmp (id, NameA, NameB, NameC)
VALUES (1, 'Value A', 'Value B', 'Value C')

SELECT
1 AS Tag,
NULL AS Parent,
ID AS [Tab!1!ID],
NameA AS [Tab!1!NameA!Element],
NULL AS [Tab2!2!NameB!Element],
NULL AS [NameC!3]
FROM @tmp
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
ID, NULL,
NameB, NULL
FROM @tmp
UNION ALL
SELECT
3 AS Tag,
1 AS Parent,
NULL, NULL, NULL,
NameC
FROM @tmp
FOR XML EXPLICIT

/*
<Tab ID="1">
<NameA>Value A</NameA>
<Tab2>
<NameB>Value B</NameB>
</Tab2>
<NameC>Value C</NameC>
</Tab>
*/

And here is the version that uses FOR XML PATH.

DECLARE @tmp TABLE (
id Int,
NameA VarChar(10),
NameB VarChar(10),
NameC VarChar(10)
)

INSERT INTO @tmp (id, NameA, NameB, NameC)
VALUES (1, 'Value A', 'Value B', 'Value C')

SELECT
id AS '@ID',
NameA AS 'NameA',
NameB AS 'Tab2/NameB',
NameC AS 'NameC'
FROM @tmp
FOR XML PATH('Tab')

<Tab ID="1">
<NameA>Value A</NameA>
<Tab2>
<NameB>Value B</NameB>
</Tab2>
<NameC>Value C</NameC>
</Tab>

Note that the query that uses FOR XML PATH is much simpler than the version using FOR XM EXPLICIT.

FOR XML Tutorials

Tags: XML, FOR_XML, FOR-XML-EXPLICIT, FOR_XML_PATH,


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



Submit

10  Comments  

  • A very useful article. Thank you. I just have a small question . I am new to back end stuff so it might be a simple issue. Here we are not defining NameC as an element. So even if ValueC is NULL, NameC tag will be displayed. Can you please help me how to get rid of the tag when ValueC is NULL.

    Thanks in Advance!

    commented on May 13 2010 5:44PM
    deepthi
    784 · 0% · 38
  • What are you using to build your XML? FOR XML PATH or EXPLICIT?

    commented on May 14 2010 3:45AM
    JacobSebastian
    47 · 4% · 1215
  • I am using FOR XML EXPLICIT

    commented on May 14 2010 2:11PM
    deepthi
    784 · 0% · 38
  • I saw your question on the forum and replied to that: http://beyondrelational.com/forums/p/3005/5767.aspx#5767

    commented on May 15 2010 2:58AM
    Jacob Sebastian
    1 · 100% · 32235
  • Hi Jacob, I need desperately need your help, I want to generate the below XML code with FOR XML PATH and want to store the result as single XML typed row of the table and retrive the only those objects that have the letter "o" Please help me, I generated below code through XML AUTO but want it through XML PATH -Jeeva

    <tables>
      <table object_id="343672272" name="stack_table" create_date="2012-11-02T19:30:00.117">
        <index name="PK__stack_ta__E117F10716644E42">
          <column name="StackId" />
        </index>
      </table>
      <table object_id="496720822" name="XmlTable" create_date="2012-12-09T09:49:56.530">
        <index name="PK__XmlTable__3214EC071F83A428">
          <column name="Id" />
        </index>
      </table>
      Above structure, I want to generate through FOR XML PATH
    
    commented on Dec 10 2012 9:23PM
    zbuntu
    2366 · 0% · 5
  • Can you show the structure of the required XML result?

    commented on Dec 11 2012 5:33AM
    Jacob Sebastian
    1 · 100% · 32235
  • Thanks for reply, Here is the structure which I want to generate through FOR XML PATH from system.

    commented on Dec 11 2012 7:01AM
    zbuntu
    2366 · 0% · 5
  • What I am trying to understand is the structure of the OUTPUT XML that you need. You posted the structure of the XML you currently able to produce. I understand that you need help to generate an XML in the required structure which your current solution does not help.

    Did I understand the problem correctly? If yes, please show the structure of the XML you would like to generate.

    One part of your post indicates that you are able to get the required output using FOR XML AUTO, but want to do with the same using PATH. Is there any specific reason why you would like to do it? Or is it just a classroom assignment? Or is it because something is wrong with the current solution?

    commented on Dec 11 2012 11:19AM
    Jacob Sebastian
    1 · 100% · 32235
  • Hi Jacob, I want below structure with FOR XML PATH

    Above structure, I want to generate through FOR XML PATH Could you please help me, how can I get the value after name? For exe, object_id is column name and 496720822 is a value in it

    commented on Dec 11 2012 2:18PM
    zbuntu
    2366 · 0% · 5
  • Thanks Jacob, I was able to figure out and resolved it. Thank you -Jeeva

    commented on Dec 12 2012 8:02AM
    zbuntu
    2366 · 0% · 5

Your Comment


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]