Solved

FOR XML EXPLICIT

Posted on 2011-09-15
8
255 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

623 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