SQL XML Syntax - 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)

SQL XML Syntax

Author

Topic

scabral7
Yak Posting Veteran

57 Posts

Posted - 2010-10-26 : 11:34:06

I'm having some issues trying to write a select statement and get the results into the correct XML format.

need the format like this:

<root>
<MeasureGroup ID = "A">
<provider>
<measure-group-total>
<count>2</count>
<rate>94.00</rate>
</measure-group-total>
<measure>
<measure-number>1</measure-number>
<eligible-instances>100</eligible-instances>
<reporting-rate>100</reporting-rate>
</measure>
<measure>
<measure-number>2</measure-number>
<eligible-instances>110</eligible-instances>
<reporting-rate>88</reporting-rate>
</measure>
</provider>
</MeasureGroup>
<MeasureGroup ID = "B">
<provider>
<measure-group-total>
<count>2</count>
<rate>100.00</rate>
</measure-group-total>
<measure>
<measure-number>3</measure-number>
<eligible-instances>100</eligible-instances>
<reporting-rate>100</reporting-rate>
</measure>
<measure>
<measure-number>4</measure-number>
<eligible-instances>110</eligible-instances>
<reporting-rate>100</reporting-rate>
</measure>
</provider>
</MeasureGroup>
</root>

So the MeasureGroup element would be a repeating element for each group and within each group the Measure element would repeat for each measure that is part of the MeasureGroup. The provider tag would also need to be wrapped around each measure. The measure-group-total element is just a calculation for each measure withing the measure group.

the data is stored in a table like this:

Measure_Group Measure_Number Eligible_INstances Reporting_Rate
A 1 100 100
A 2 110 88
B 3 100 100
B 4 110 100

I'm new to writing the XML using For XML, so the repeating groups and repeating elements within the groups are frustrating me. Any ideas would be appreciated.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-10-26 : 12:59:41

what consumes this xml data? a .net app or web app? mvc?

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

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-10-26 : 13:01:34

in order for people to help you you will need to provide the following so that they do not have to recreate your scenario.


CREATE TABLE giveme (Measure_Group NVARCHAR(22), Measure_Number INT , Eligible_INstances int, Reporting_Rate int)
INSERT INTO giveme
SELECT 'A', 1, 100, 100
UNION ALL
SELECT 'A', 2, 110, 88
UNION ALL
SELECT 'B', 3, 100, 100
UNION ALL
SELECT 'B', 4, 110, 100

Try this for starters


SELECT Measure_Group MeasureGroupID,SUM(Measure_Number) 'measure-group-total', COUNT(*) icount, Eligible_INstances ,Reporting_Rate
  FROM mama
GROUP BY Measure_Group, Measure_Number, Eligible_INstances, Reporting_Rate
FOR XML PATH(''),ROOT

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

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-10-26 : 13:17:01

and the final FOR XML from http://msdn.microsoft.com/en-us/library/ms345137%28SQL.90%29.aspx#forxml2k5_topic6


SELECT Measure_Group AS '@ID',
       COUNT(*) AS "measure-group-total/count", 
       Reporting_Rate AS "measure-group-total/rate",  
       Measure_Number AS "measure/measure_number",
       Eligible_INstances AS "measure/eligible-instances"
  FROM mama
 GROUP BY Measure_Group, Measure_Number, Eligible_INstances, Reporting_Rate
   FOR XML PATH('MeasureGroup'),ROOT('root')

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

scabral7
Yak Posting Veteran

57 Posts

Posted - 2010-10-26 : 13:35:39

thanks yosiasz,

it's very close, but the MeasureGroup tag is being recreated for every measure and i only need it created for each measure that is part of the Measure Group. For example, measure 1 and measure 2 should be wrapped within <MeasureGroup = "A"> since they are part of the "A" measure group. What is happening is both measure 1 and 2 are getting wrapped within <MeasureGroup = "A"> separately.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-10-26 : 15:45:25

maybe this? what is up with the provider tag, seems redundant not useful


  SELECT Measure_Group AS '@ID',
       (SELECT COUNT(*)
          FROM mama cnt 
         WHERE cnt.Measure_Group =  xm.Measure_Group GROUP BY Measure_Group) "measure-group-total/count", 
       (SELECT AVG(Reporting_Rate)
          FROM mama cnt 
         WHERE cnt.Measure_Group =  xm.Measure_Group GROUP BY Measure_Group) "measure-group-total/rate",          
       (SELECT Measure_Number,Eligible_INstances,Reporting_Rate
          FROM mama cnt 
         WHERE cnt.Measure_Group =  xm.Measure_Group FOR XML PATH('measure'), TYPE )          
  FROM mama xm
 GROUP BY Measure_Group
  FOR XML PATH('MeasureGroup'),ROOT('root')

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