SQL For XML Array with Header and Multiple Detail

  • sadie

    SSC Veteran

    Points: 252

    How do I get the following output XML using SQL?
    <Base>
    <Header>
      <HdrAttrib1>1021156</HdrAttrib1>
      <HdrAttrib2>A new item</HdrAttrib1>
      <Detail>
      <DetailRow>
       <DtlAttrib1>0</DtlAttrib1>
       <DtlAttrib2>0</DtlAttrib2>
            <DtlAttrib3>34</DtlAttrib3>
      </DetailRow>
         <DetailRow>
       <DtlAttrib1>1</DtlAttrib1>
       <DtlAttrib2>2</DtlAttrib2>
            <DtlAttrib3>34</DtlAttrib3>
      </DetailRow>
         <DetailRow>
       <DtlAttrib1>1</DtlAttrib1>
       <DtlAttrib2>5</DtlAttrib2>
            <DtlAttrib3>34</DtlAttrib3>
      </DetailRow>
      </Detail>
    </Header>
    </Base>

  • Scott Coleman

    One Orange Chip

    Points: 27375

    This works with all the values hardcoded.  In practice, the Detail subquery would probably be a correlated subquery based on something in the header row.
    SELECT HdrAttrib1,
       HdrAttrib2,
       Detail = (
        SELECT DtlAttrib1, DtlAttrib2, DtlAttrib3
        FROM (
          VALUES (0,0,34), (1,2,34), (1,5,34)
        ) details (DtlAttrib1, DtlAttrib2, DtlAttrib3)
        FOR XML PATH('DetailRow'),TYPE)
    FROM (
      VALUES (1021156, 'A new item')
    ) head (HdrAttrib1, HdrAttrib2)
    FOR XML PATH('Header'), ROOT('Base')

    <Base>
    <Header>
      <HdrAttrib1>1021156</HdrAttrib1>
      <HdrAttrib2>A new item</HdrAttrib2>
      <Detail>
      <DetailRow>
       <DtlAttrib1>0</DtlAttrib1>
       <DtlAttrib2>0</DtlAttrib2>
       <DtlAttrib3>34</DtlAttrib3>
      </DetailRow>
      <DetailRow>
       <DtlAttrib1>1</DtlAttrib1>
       <DtlAttrib2>2</DtlAttrib2>
       <DtlAttrib3>34</DtlAttrib3>
      </DetailRow>
      <DetailRow>
       <DtlAttrib1>1</DtlAttrib1>
       <DtlAttrib2>5</DtlAttrib2>
       <DtlAttrib3>34</DtlAttrib3>
      </DetailRow>
      </Detail>
    </Header>
    </Base>

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply