Solved

FOR XML EXPLICIT

Posted on 2011-09-15
8
243 Views
Last Modified: 2012-05-12
I have a fairly complex SQL Statement to create some XML using the FOR XML EXPLICIT clause.  But I am unsure how to accomplish the following.

<LEVEL 00>
<LEVEL 01>
     <DATA 01-01>A</DATA 01-01>
     <DATA 01-02>B</DATA 01-02>
     <DATA 01-03>C</DATA 01-03>
          <LEVEL 02>
               <DATA 02-01>D</DATA 02-01>
               <DATA 02-02>E</DATA 02-02>
          </LEVEL 02>
     <DATA 01-04>F</DATA 01-04>
     <DATA 01-05>G</DATA 01-05>
          <LEVEL 03>
               <DATA 03-01>H</DATA 03-01>
               <DATA 03-02>I</DATA 03-02>
          </LEVEL 03>
          <LEVEL 03>
               <DATA 03-01>J</DATA 03-01>
               <DATA 03-02>K</DATA 03-02>
          </LEVEL 03>
     <DATA 01-06>L</DATA 01-06>
</LEVEL 01>
</LEVEL 00>

There will be one and only one instance on LEVEL 02 for each instance of LEVEL 01.
There will be none, one, or many instances on LEVEL 03 for each instance of LEVEL 01.

Currently, my statement is generating a </LEVEL 02> before generating a <DATA 01-04>, completing the record before all the data has been associated.

I am using the TAG and PARENT constructs.  Here is how I have them grouped:

LEVEL 00: T1, P0
LEVEL 01: T2, P1 for DATA 01-01 through DATA 01-03 only
LEVEL 02: T3, P2
LEVEL 01: T4, P1 for DATA 01-04 through DATA 01-05 only
LEVEL 03: T5, P2

Where T is TAG and P is PARENT.

Can anyone provide me any idea what I might be doing incorrectly?

Thanks In Advance!
0
Comment
Question by:mjs082969
  • 4
  • 4
8 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Do you mind posting the query you have?
Additionally, if you have the original data structure and sample data, then we might be able to help advise how to construct this also.
0
 

Author Comment

by:mjs082969
Comment Utility
mwvisa,

Thanks for the response.  I think I was able to work it out myself.  But could you verify this?

The SQL statement that creates the XML is massive.  Either the approach I have chosen is not the best (very possible), or this can be a very tricky situation.

My XML has about 50 fields.  The SQL statement that creates the XML is approximately 3000 rows.  I have 50 tags, one for each field, and each field is listed in every tag (~50 x ~50) so if you add a little whitespace, you're hitting 3000.

Is this completely crazy?  This has turned into Codezilla.

It does work.  But that's about all the good I can say about my solution.  Not very elegant.
 
0
 

Author Comment

by:mjs082969
Comment Utility


P.S.  This is where the steaming mess that is my solution originated....

http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-programming/33527/Tags-in-particular-order-using-FOR-XML-EXPLICIT

It is modelled very closely to solution suggested.  But where the person who posed the question had 10 fields, I have 50.  
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
There are ways to do this with FOR XML without the UNION approach of tags in EXPLICIT, which is why I was wondering about "the original data structure and sample data." You can sometimes simulate the hierarchy through nested FOR XML through sub-queries, but would have to try your specific case to see if it applies.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:mjs082969
Comment Utility

Thanks,

I can't post the data structure or sample data as it is somewhat confidential.  Can you direct me to documentation or examples regarding the non-UNION approaches?

0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Look at FOR XML AUTO and FOR XML PATH.

In general:

SELECT col1 AS Data1
     , ({sub query} FOR XML PATH('Level2'))
...
FOR XML PATH('Level1'), ROOT('Level0')

You use sub-queries to built the levels.
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
Comment Utility
You can also use a FOR XML PATH('') << EMPTY Element Name
Then set the top-level element name as the alias. This can be used to build further structure, i.e., combination of PATH(), ROOT(), and column ALIAS in outer query.

Here is a more concrete example:
;with tbl_level_one(id, col1) as (
   select 1, 'x' union 
   select 2, 'y' union 
   select 3, 'z'
), tbl_level_two(id, fk, col1) as (
   select 1, 1, 'xx' union 
   select 2, 1, 'xy' union
   select 3, 1, 'xz'
), tbl_level_three(id, fk, col1) as (
   select 1, 2, 'yx' union 
   select 2, 2, 'yy' union
   select 3, 2, 'yz'
)
select ( /* wrapper to get level 0 */

/* get level one values */
select id as "@id", col1 as data
     , ( /* get level 2 values */

	 select id as "@id", col1 as data
	 from tbl_level_two t2
	 where t2.fk = t1.id
	 for xml path('level2'), type
	 
	 )
     , ( /* get level 3 values */
	 
	 select id as "@id", col1 as data
	 from tbl_level_two t3
	 where t3.fk = t1.id
	 for xml path('level3'), type

	 )
from tbl_level_one t1
for xml path('level1'), type

) for xml path('level0'), type

Open in new window


Results:
<level0>
  <level1 id="1">
    <data>x</data>
    <level2 id="1">
      <data>xx</data>
    </level2>
    <level2 id="2">
      <data>xy</data>
    </level2>
    <level2 id="3">
      <data>xz</data>
    </level2>
    <level3 id="1">
      <data>xx</data>
    </level3>
    <level3 id="2">
      <data>xy</data>
    </level3>
    <level3 id="3">
      <data>xz</data>
    </level3>
  </level1>
  <level1 id="2">
    <data>y</data>
  </level1>
  <level1 id="3">
    <data>z</data>
  </level1>
</level0>

Open in new window


Good luck!

Best regards and happy coding,

Kevin
0
 

Author Closing Comment

by:mjs082969
Comment Utility
Thanks, you were very helpful...

I wish I could give more points.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now