Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

FOR XML EXPLICIT

Posted on 2011-09-15
8
Medium Priority
?
260 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 60

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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 60

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 60

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 60

Accepted Solution

by:
Kevin Cross earned 2000 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

Monthly Recap

May was a big month for new releases from Linux Academy! Take a look at what our team built recently in our blog. You can access the newest releases from our blog.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

661 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