XML grouping and node header - 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 2008 Forums

Transact-SQL (2008)

XML grouping and node header

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-06-02 : 14:48:19

Greetings,

given the following XML issue


declare @attributes table(attribute_id int, 
                          attribute_name nvarchar(255), 
                          attribute_type int)

insert into @attributes
SELECT 1, 'blue', 1
UNION 
SELECT 2, 'red', 1
UNION
SELECT 3, 'green', 1
UNION
SELECT 4,'black', 2
UNION 
SELECT 5,'yellow', 2
UNION
SELECT 6,'violet', 2
UNION
SELECT 7,'grey', 3
UNION 
SELECT 8,'karaloo', 3
UNION
SELECT 9,'mango', 4


SELECT attribute_id '@attribute_id',
       attribute_name '@attribute_name',
       attribute_type '@attribute_type'
 FROM @attributes
 FOR XML PATH('attribute'), root('attributes')

how could I get the following xml result so that attributes are grouped


<attributes>
  <tropicals>
    <attribute attribute_id="1" attribute_name="blue" attribute_type="1" />
    <attribute attribute_id="2" attribute_name="red" attribute_type="1" />
    <attribute attribute_id="3" attribute_name="green" attribute_type="1" />
  </tropicals>  
  <northern>
    <attribute attribute_id="4" attribute_name="black" attribute_type="2" />
    <attribute attribute_id="5" attribute_name="yellow" attribute_type="2" />
    <attribute attribute_id="6" attribute_name="violet" attribute_type="2" />
  </northern>
  <european>
    <attribute attribute_id="7" attribute_name="grey" attribute_type="3" />
    <attribute attribute_id="8" attribute_name="karaloo" attribute_type="3" />
  </european>
  <delicious>
    <attribute attribute_id="9" attribute_name="mango" attribute_type="4" />
  </delicious>    
</attributes>

I could do it with CASE attribute_id but that would break when a new attribute_id comes in. So I want it to be dynamic.

If you don't have the passion to help people, you have no passion

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-06-02 : 15:03:11

Do you have an attribute_type table that has the descriptions? You can then join that by attribute_type to the attributes table.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-06-02 : 15:08:23

thanks for response.
yes I do have an attribute_type table and I am joining to it but how do I get the final xml result?

If you don't have the passion to help people, you have no passion

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-06-02 : 15:20:47

I haven't done a lot with XML PATH, from what I've seen in Books Online you may need to use FOR XML EXPLICIT to get the nesting you're looking for.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-06-02 : 15:30:51

I was afraid of that. I tried it and I am crossed eyed now because of XML EXPLICIT

If you don't have the passion to help people, you have no passion

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-02 : 20:12:44

Can you post how you did it using XML EXPLICIT? I don't know how to use column values as node names in any of the FOR XML options.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-02 : 20:27:52

This is the only thing I can think of. Sure, it's ugly, but don't laugh, it works, and it is simple


declare @attributes table(attribute_id int, 
                          attribute_name nvarchar(255), 
                          attribute_type int)

insert into @attributes
SELECT 1, 'blue', 1
UNION 
SELECT 2, 'red', 1
UNION
SELECT 3, 'green', 1
UNION
SELECT 4,'black', 2
UNION 
SELECT 5,'yellow', 2
UNION
SELECT 6,'violet', 2
UNION
SELECT 7,'grey', 3
UNION 
SELECT 8,'karaloo', 3
UNION
SELECT 9,'mango', 4

declare @attribute_types table (attribute_type INT, attribute_type_name VARCHAR(32));
insert into @attribute_types
SELECT 1, 'tropicals' UNION SELECT 2, 'northern' UNION SELECT 3, 'european' UNION  SELECT 4,'delicious';
 
SELECT
   CAST('<' + attribute_type_name + '>' + c + '</' + attribute_type_name + '>' AS XML)
FROM
   @attribute_types at 
   CROSS APPLY 
   (
      SELECT 
         attribute_id '@attribute_id',
         attribute_name '@attribute_name',
         attribute_type '@attribute_type'
      FROM
         @attributes a
      WHERE
         a.attribute_type = at.attribute_type
      FOR XML PATH('attribute') 
   ) T(c)
FOR XML PATH('') , root('attributes')

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-06-03 : 11:06:09

Hey Sunitha

Thanks! I will try your idea, I can tweak it a bit to fit my scenario!

Here you go with FOR XML EXPLICIT. I bet you are drooling since we know you love FOR XML EXPLICIT so much!


declare @attributes table(attribute_id int, 
                          attribute_name nvarchar(255), 
                          attribute_type int)

insert into @attributes
SELECT 1, 'blue', 1
UNION 
SELECT 2, 'red', 1
UNION
SELECT 3, 'green', 1
UNION
SELECT 4,'black', 2
UNION 
SELECT 5,'yellow', 2
UNION
SELECT 6,'violet', 2
UNION
SELECT 7,'grey', 3
UNION 
SELECT 8,'karaloo', 3
UNION
SELECT 9,'mango', 4



 
 SELECT
    1 AS Tag,
    NULL AS Parent,
    NULL AS 'attributes!1!',
    NULL AS 'tropicals!2',
    NULL AS 'attribute!3!attribute_name',
    NULL AS 'attribute!3!attribute_id',
    NULL AS 'attribute!3!attribute_type'
UNION ALL
SELECT
    2 AS Tag,
    1 AS Parent,
    NULL,			--'attributes!1!',
    NULL ,			--'attribute!2',
    NULL ,
    NULL,
    NULL
 UNION ALL
SELECT
    3 AS Tag,
    2 AS Parent,
    NULL,			--'attributes!1!',
    NULL ,			--'attribute!2',
    attribute_name ,
    attribute_id,
    attribute_type
 FROM @attributes 
 Where attribute_type =1        
 FOR XML EXPLICIT

If you don't have the passion to help people, you have no passion

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-03 : 11:29:42

Of course, I love XML EXPLICIT (not!!!)

In your code, the node name tropicals is hard-coded, so wouldn't you need to repeat this process for each of the attribute_type_names? What I was trying to do was read-off the attribute_type_name from a table and construct the XML. So if you add another attribute type to the table, the query would/should work without any changes.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-06-03 : 12:22:00

exactly !!! I am carving this out as we speak. Do not be afraid of the dark side of FOR XML EXPLICIT.

Thank you Sunita

If you don't have the passion to help people, you have no passion

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-03 : 13:16:09

You know.... it's not just me who is afraid of the dark side of XML EXPLICIT. Microsoft got so scared by the monster they created that they decided to deprecate it.

I still can't imagine a way you can automatically include the data in a column as a node name even with XML EXPLICIT - unless you use dynamic SQL or do something unsavory like I did with the XML PATH in my earlier post.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-06-03 : 13:36:31

yes I agree it should be defecated I mean deprecated!! your stuff is very cool! dynamic sql is also an option or dump data to table then FOR XML PATH it.

If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources