SQL For XML Array with Header and Multiple Detail

• sadie

  Old Hand

  Points: 340

  More actions

  December 11, 2018 at 12:32 pm

  #104560

  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: 27452

  More actions

  December 11, 2018 at 1:44 pm

  #2016027

  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>