Solved

FOR XML EXPLICIT

Posted on 2011-09-15
8
245 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
ID: 36545944
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
ID: 36551298
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
ID: 36551318


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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36552356
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
 

Author Comment

by:mjs082969
ID: 36559956

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
ID: 36561816
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
ID: 36562002
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
ID: 36573722
Thanks, you were very helpful...

I wish I could give more points.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

808 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