FOR XML PATH count(*) on top node

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)

FOR XML PATH count(*) on top node

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-05-16 : 18:44:02

Greetings,

I would like the following results when doing FOR XML PATH

<attributes count="24">
<attribute>What</attribute>
<attribute>is</attribute>
<attribute>the</attribute>
<attribute>answer</attribute>
...
</attributes>

How do I got about doing that?

Thanks!

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-16 : 19:42:29

yosiasz! where you been!?! glad you are back!!

Are you trying to insert the count as an attribute into the attributes node? Would this do it?


set xmlCol.modify
(
   'insert attribute count {count(./attributes/attribute)} into (./attributes)[1]'
);

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-05-16 : 19:45:20

watup!!! been buried with work (good thing) and extremely busy! Well I would rather not do an insert row_number() OVER works fine but it embeds it in each element. I wanted just one pretty little count on top node. I will try your stuff. You love xml don't you?

maybe I should go with a combination of cte and 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-05-16 : 19:58:30

XML explicit? omg, no yosiasz, that is like torture!!

Me? love XML? No way!! I think of it like cancer (or may be mold) - growing and spreading everywhere. It has no place in relational databases, if you ask me. But, wouldn't you know it, they didn't ask me!! So I put up with it, just like you do

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-05-16 : 20:03:46

well you are very good at it Sunita! I think cte might work and or a subquery...


SELECT ProductModelID       AS "@ProductModelID",
       Name                 AS "@ProductModelName",
      (SELECT ProductID AS "data()"
       FROM   Production.Product
       WHERE  Production.Product.ProductModelID = 
              Production.ProductModel.ProductModelID
      FOR XML PATH (''))    AS "@ProductIDs"
FROM  Production.ProductModel
WHERE ProductModelID= 7 
FOR XML PATH('ProductModelData');

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-16 : 20:17:33

May be another subquery like this?


SELECT ProductModelID       AS "@ProductModelID",
       Name                 AS "@ProductModelName",
      (SELECT ProductID AS "data()"
       FROM   Production.Product
       WHERE  Production.Product.ProductModelID = 
              Production.ProductModel.ProductModelID
      FOR XML PATH (''))    AS "@ProductIDs",
      (SELECT COUNT(*) AS "data()"
       FROM   Production.Product
       WHERE  Production.Product.ProductModelID = 
              Production.ProductModel.ProductModelID
      FOR XML PATH (''))    AS "@ProductCount"
FROM  Production.ProductModel
WHERE ProductModelID= 7 
FOR XML PATH('ProductModelData');

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-05-17 : 11:27:16

sad to report that this is not working out. so tell me more about


set xmlCol.modify
(
   'insert attribute count {count(./attributes/attribute)} into (./attributes)[1]'
);

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-17 : 11:58:14

What about it is not working? Is it calculating the wrong count? In any case, the modify method adds the count attribute after you have created and populated the xml column. For example, like this:

DECLARE @xml XML;
SET @xml = 
'<attributes>
  <attribute>What</attribute>
  <attribute>is</attribute>
  <attribute>the</attribute>
  <attribute>answer</attribute>
</attributes>';
SELECT @xml;

SET @xml.modify
(
   'insert attribute count {count(./attributes/attribute)} into (./attributes)[1]'
);

SELECT @xml;

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-05-17 : 14:45:02

apologies I have been away for so long I have even forgotten how to post questions the sqlteam way.
my source data is sql server table. my ultimate plan is to spit out xml files using ssis. my query gathers values from different tables. on some of the xml nodes I need to capture counts on certain elements. I would like to avoid counting the value into a var and then stuff it in the xml. I want to capture the counts dynamically. here is some sample data. I want this count value in the root node


declare @attributes table(attribute_name nvarchar(255))

insert into @attributes
SELECT 'What'
UNION
SELECT 'is'
UNION
SELECT 'the'
UNION
SELECT 'answer'
UNION
SELECT 'please'


SELECT @@rowcount as '@count', attribute_name  as name
FROM @attributes FOR XML PATH('att') , root('attributes')

Thanks

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-17 : 15:03:10

Would this work? If you have more columns that need to be grouped, I think they can be accommodated in this approach.


SELECT
   COUNT(*) AS [@count],
   (SELECT
      attribute_name AS [text()]
    FROM
      @attributes
   FOR XML PATH('attr'),TYPE
   )
FROM
   @attributes
FOR XML PATH('attributes') ;

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-05-17 : 16:25:58

aha!! i told you you liked xml. this works perfectly

thank you very much!

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-18 : 07:09:12

quote:
Originally posted by yosiasz

apologies I have been away for so long I have even forgotten how to post questions the sqlteam way.

Glad it worked out, yosiasz!! And, this explains it all! I was beginning to worry about you!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources