FOR XML - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

FOR XML

Author

Topic

dentyne
Starting Member

2 Posts

Posted - 2009-03-16 : 11:21:09

I have a requirement for a user to be able to view data in a table in different hierarchical ways. 1. I want to return XML from SQL Server 2005 and then use this in a TreeView control on the client side.

2. I want to use the FOR XML in SQL Server 2005. Since there are 10 keys in the table and they can be viewed in any hierarchical way, I feel using Explicit would just be way too unruly and I don't really understand how it works.

3. I can best explain my question by supplying the script to create a small table with sample data, then mentioning how I would like the XML to look based on how the user wants to see their hierarchy. Please see the section below for the sample script and the desired XML format.

Here's what I tried, assuming the user wants the hierarchy SBU/COMPANY/COUNTRY. But unfortunately SBU repeats in the XML.

SELECT SBU,
COMPANY AS "SBU/COMPANY",
COUNTRY AS "SBU/COMPANY/COUNTRY"
FROM zHRCHY_TEST
ORDER BY SBU, COMPANY, COUNTRY
FOR XML PATH('SBU'),ROOT('DOC');

Thanks for the help. This one is giving me a tough time.

/*--SCRIPT TO CREATE SAMPLE TABLE AND DATA --*/
CREATE TABLE zHRCHY_TEST
(
SBU VARCHAR(20),
COMPANY VARCHAR(20),
COUNTRY VARCHAR(20)
)

INSERT INTO zHRCHY_TEST
VALUES('SBU-A', 'COMPANY-123', 'US');
INSERT INTO zHRCHY_TEST
VALUES('SBU-A', 'COMPANY-123', 'CA');
INSERT INTO zHRCHY_TEST
VALUES('SBU-A', 'COMPANY-123', 'MX');
INSERT INTO zHRCHY_TEST
VALUES('SBU-A', 'COMPANY-456', 'CA');
INSERT INTO zHRCHY_TEST
VALUES('SBU-A', 'COMPANY-789', 'FR');
INSERT INTO zHRCHY_TEST
VALUES('SBU-B', 'COMPANY-123', 'US');
INSERT INTO zHRCHY_TEST
VALUES('SBU-B', 'COMPANY-456', 'CA');
INSERT INTO zHRCHY_TEST
VALUES('SBU-B', 'COMPANY-789', 'FR');
INSERT INTO zHRCHY_TEST
VALUES('SBU-C', 'COMPANY-987', 'MX');
INSERT INTO zHRCHY_TEST
VALUES('SBU-C', 'COMPANY-654', 'VZ');
INSERT INTO zHRCHY_TEST
VALUES('SBU-C', 'COMPANY-321', 'CH');

SELECT * FROM zHRCHY_TEST;

/*-- When the user's hierarchy is SBU/COMPANY/COUNTRY
<SBU>SBU-A
<COMPANY>COMPANY-123
<COUNTRY>US</COUNTRY>
<COUNTRY>CA</COUNTRY>
<COUNTRY>MX</COUNTRY>
</COMPANY>
<COMPANY>COMPANY-456
<COUNTRY>CA</COUNTRY>
</COMPANY>
<COMPANY>COMPANY-789
<COUNTRY>FR</COUNTRY>
</COMPANY>
</SBU>
<SBU>SBU-B
<COMPANY>COMPANY-456
etc. etc.
*/

/*--When the user's hierarchy is COMPANY/SBU/COUNTRY
<COMPANY>COMPANY-456
<SBU>SBU-A
<COUNTRY>CA</COUNTRY>
</SBU>
<SBU>SBU-B
<COUNTRY>CA</COUNTRY>
</SBU>
</COMPANY>
etc. etc.
*/

/*-- When the user's hierarchy is COUNTRY/COMPANY/SBU
<COUNTRY>US
<COMPANY>COMPANY-123
<SBU>SBU-A</SBU>
<SBU>SBU-B</SBU>
</COMPANY>
<COUNTRY>
etc. etc.
*/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-16 : 11:29:50

seems like what you need is FOR XML EXPLICIT to specify order each time

dentyne
Starting Member

2 Posts

Posted - 2009-03-16 : 14:35:49

I sure hope I don't have to go the EXPLICIT way. I kind of toyed around with this query that looks like it has some promise, but it seems to double nest the SBU, COUNTRY, and COUNTRY tags by wrapping them in an extra layer. Any ideas?

SELECT a.SBU,
(
SELECT b.COMPANY,
(
SELECT c.COUNTRY
FROM zHRCHY_TEST c
WHERE c.COMPANY = b.COMPANY
GROUP BY c.COUNTRY
FOR XML PATH('COUNTRY'), TYPE
)
FROM zHRCHY_TEST b
WHERE b.SBU = a.SBU
GROUP BY b.COMPANY
FOR XML PATH('COMPANY'),TYPE
)
FROM zHRCHY_TEST a
GROUP BY a.SBU
FOR XML PATH('SBU'),TYPE, ROOT('DOC')

<DOC>
<SBU>
<SBU>SBU-A</SBU>
<COMPANY>
<COMPANY>COMPANY-123</COMPANY>
<COUNTRY>
<COUNTRY>CA</COUNTRY>
</COUNTRY>
<COUNTRY>
<COUNTRY>MX</COUNTRY>
</COUNTRY>
<COUNTRY>
<COUNTRY>US</COUNTRY>
</COUNTRY>
</COMPANY>
<COMPANY>
<COMPANY>COMPANY-456</COMPANY>
<COUNTRY>
<COUNTRY>CA</COUNTRY>
</COUNTRY>
</COMPANY>
<COMPANY>
<COMPANY>COMPANY-789</COMPANY>
<COUNTRY>
<COUNTRY>FR</COUNTRY>
</COUNTRY>
</COMPANY>
</SBU>

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-03-16 : 17:51:12

how about this

SELECT COMPANY ,
SBU "COMPANY/SBU",
COUNTRY "COMPANY/SBU/COUNTRY"
FROM zHRCHY_TEST
GROUP BY COMPANY, SBU, COUNTRY
ORDER BY ComPANY
FOR XML PATH(''), TYPE, ROOT('DOC')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources