• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 270
  • Last Modified:

FOR XML EXPLICIT

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
mjs082969
Asked:
mjs082969
  • 4
  • 4
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
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
 
mjs082969Author Commented:
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
 
mjs082969Author Commented:


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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Kevin CrossChief Technology OfficerCommented:
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
 
mjs082969Author Commented:

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
 
Kevin CrossChief Technology OfficerCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
mjs082969Author Commented:
Thanks, you were very helpful...

I wish I could give more points.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now