Results 1 to 4 of 4

Thread: SQL XML syntax

  1. #1
    Join Date
    Sep 2009
    Posts
    62

    Unanswered: SQL XML syntax

    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:

    Code:
    <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.
    Last edited by pootle flump; 10-27-10 at 11:50.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Please could you write your table and data as DDL create table and DML insert statements?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2009
    Posts
    62
    CREATE TABLE MyTable (Measure_Group NVARCHAR(22), Measure_Number INT , Eligible_INstances int, Reporting_Rate int)
    INSERT INTO MyTable
    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

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I've edited your post to format the result correctly
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •