Formatting SQL Query to XML File

Question

I am trying to convert a SQL query to an XML file. I am however having an issue with "Compliance" containing "CompliesWith" as seen below. What is happening is there are multiple "CompliesWith" and instead of there being 4 lines within "Compliance" it is making iterations. Does anyone know how to correct this? Please see below for query and XML output.

select 

    (
    select [SpecNum] = ss.SpecNum
         , [IssueNum] = RTRIM(ss.IssueNum)
         , [SpecStatus] = wfs.status
         , [PIM_DEV_RC_ID] = ''
         , [PIM_DEV_SU_ID] = ''
         , [PIM_Supplier_Item] = ''
         , [WRIN] = coalesce(wrin.WRIN, '')
         , [WSI] = coalesce(wsi.WSI, '')
         , [SpecName] = nm.name
         , [ShortName] = snm.Name

         , (
             select [NutrientNameId] = np.InFoodsID
                  , [NutrientName] = np.InFoodsID
                  , [NutrientPer100gUnrounded] = ROUND(ni.PerGramMeasure, 1)        
                  , [NutrientPer100gRounded] = CAST(ni.PerGramMeasure as char(5))   
             from gsmNutrientItem ni
               join comStandardNutrientProperties np on ni.fkNutrient = np.pkid
             where ss.SpecID = ni.fkSpecID

               and np.InFoodsID in ('ENERC_KJ','ENERC_KCAL','PROCNT','FAT','NACL')
             FOR XML RAW ('NutrientFacts'), TYPE, ROOT('NutritionDetails'), ELEMENTS-- XSINIL
           )

         , (
             select [CompliesWith] = cw.name
             from SpecCompliesWithJoin cwj
               join CompliesWith cw on cwj.fkCompliesWith = cw.pkid 
                                   and cw.langID = 0
             where ss.SpecID = cwj.fkSpec 
             FOR XML RAW ('CompliesWith'), TYPE, ROOT('Compliance'), ELEMENTS
           )

         , (
             select [AllergenID] = lk.ExternalId
                  , [AllergenName] = a.name
                  , [AllergenMaxPer100g] = ac.MaxPer100g
             from AllergensContained ac
               join Allergens a on ac.fkAllergen = a.pkid
                               and a.langID = 0
               join commonExternalIDLookup lk on ac.fkAllergen = lk.fkLookupItem
--             where ss.SpecID = ac.fkParent 
             where ac.fkParent = '6501d6e18a21-15b0-44a6-bcc4-b00c164e0c32'
             FOR XML RAW ('AllergensKnownToContain'), TYPE, ROOT('Allergens'), ELEMENTS
           )
    from specSummary ss
      join SpecSummaryName nm on ss.PKID = nm.fkSpecsummary and nm.langid = 0
      join gsmShortNameML snm on ss.PKID = snm.fkSpecsummary and snm.langid = 0
      join commonWorkflowStatus wfs on ss.SpecStatusID = wfs.pkid and wfs.langID = 0

      left outer join ( select lsj.fkSpecID
                             , lsj.Equivalent as wrin
                             , ROW_NUMBER () OVER(PARTITION BY lsj.fkSpecID order by lsj.equivalent) as rn
                        from specLegacySpecJoin lsj
                          join specLegacyProfile lp on lsj.fkLegacyProfileID = lp.PKID and lp.SystemCode = 'WRIN'
                      ) wrin on ss.SpecID = wrin.fkSpecID and wrin.rn = 1

      left outer join ( select lsj.fkSpecID
                             , lsj.Equivalent as wsi
                        from specLegacySpecJoin lsj
                          join specLegacyProfile lp on lsj.fkLegacyProfileID = lp.PKID and lp.SystemCode = 'WSI'
                      ) wsi on ss.SpecID = wsi.fkSpecID

    where nm.name in ('Dairy - Shake Mix - 2.5% Fat - UHT - Arla Food - Denmark'
                     ,'Sauce - Creamy Bell Pepper, Chilli and Lime Sauce - Develey - Germany - Dingolfling'
                     )
    FOR XML RAW ('ID'), TYPE, ROOT('Record')
    )
FOR XML RAW ('Records')

<Records>
  <Record>
    <ID SpecNum="5069580" IssueNum="001" SpecStatus="Pre-Approved" PIM_DEV_RC_ID="" PIM_DEV_SU_ID="" PIM_Supplier_Item="" WRIN="11996 - 005" WSI="" SpecName="Sauce - Creamy Bell Pepper, Chilli and Lime Sauce - Develey - Germany - Dingolfling" ShortName=" Creamy Bell Pepper, Chilli &amp; Lime Sauce">
      <NutritionDetails>
        <NutrientFacts>
          <NutrientNameId>ENERC_KCAL</NutrientNameId>
          <NutrientName>ENERC_KCAL</NutrientName>
          <NutrientPer100gUnrounded>1.910000000000000e+002</NutrientPer100gUnrounded>
          <NutrientPer100gRounded>191  </NutrientPer100gRounded>
        </NutrientFacts>
        <NutrientFacts>
          <NutrientNameId>ENERC_KJ</NutrientNameId>
          <NutrientName>ENERC_KJ</NutrientName>
          <NutrientPer100gUnrounded>7.910000000000000e+002</NutrientPer100gUnrounded>
          <NutrientPer100gRounded>791  </NutrientPer100gRounded>
        </NutrientFacts>
        <NutrientFacts>
          <NutrientNameId>PROCNT</NutrientNameId>
          <NutrientName>PROCNT</NutrientName>
          <NutrientPer100gUnrounded>1.500000000000000e+000</NutrientPer100gUnrounded>
          <NutrientPer100gRounded>1.5  </NutrientPer100gRounded>
        </NutrientFacts>
        <NutrientFacts>
          <NutrientNameId>NACL</NutrientNameId>
          <NutrientName>NACL</NutrientName>
          <NutrientPer100gUnrounded>1.200000000000000e+000</NutrientPer100gUnrounded>
          <NutrientPer100gRounded>1.21 </NutrientPer100gRounded>
        </NutrientFacts>
        <NutrientFacts>
          <NutrientNameId>FAT</NutrientNameId>
          <NutrientName>FAT</NutrientName>
          <NutrientPer100gUnrounded>1.520000000000000e+001</NutrientPer100gUnrounded>
          <NutrientPer100gRounded>15.2 </NutrientPer100gRounded>
        </NutrientFacts>
      </NutritionDetails>
      <Compliance>
        <CompliesWith>
          <CompliesWith>SQMS</CompliesWith>
        </CompliesWith>
        <CompliesWith>
          <CompliesWith>Gluten free</CompliesWith>
        </CompliesWith>
        <CompliesWith>
          <CompliesWith>Allergens Checked</CompliesWith>
        </CompliesWith>
        <CompliesWith>
          <CompliesWith>Vegetarian</CompliesWith>
        </CompliesWith>
        <CompliesWith>
          <CompliesWith>Lactose Free</CompliesWith>
        </CompliesWith>
        <CompliesWith>
          <CompliesWith>Non-GM</CompliesWith>
        </CompliesWith>
      </Compliance>
      <Allergens>
        <AllergensKnownToContain>
          <AllergenID>MILK (INCLUDING LACTOSE)/COW'S MILK</AllergenID>
          <AllergenName>Milk (including lactose)/Cow's milk</AllergenName>
          <AllergenMaxPer100g>9.000500000000000e+001</AllergenMaxPer100g>
        </AllergensKnownToContain>
      </Allergens>
    </ID>
    <ID SpecNum="5002206" IssueNum="001" SpecStatus="Review" PIM_DEV_RC_ID="" PIM_DEV_SU_ID="" PIM_Supplier_Item="" WRIN="" WSI="" SpecName="Dairy - Shake Mix - 2.5% Fat - UHT - Arla Food - Denmark" ShortName="2.5% Fat Shake Mix">
      <NutritionDetails>
        <NutrientFacts>
          <NutrientNameId>FAT</NutrientNameId>
          <NutrientName>FAT</NutrientName>
          <NutrientPer100gUnrounded>2.400000000000000e+000</NutrientPer100gUnrounded>
          <NutrientPer100gRounded>2.4  </NutrientPer100gRounded>
        </NutrientFacts>
        <NutrientFacts>
          <NutrientNameId>PROCNT</NutrientNameId>
          <NutrientName>PROCNT</NutrientName>
          <NutrientPer100gUnrounded>3.300000000000000e+000</NutrientPer100gUnrounded>
          <NutrientPer100gRounded>3.3  </NutrientPer100gRounded>
        </NutrientFacts>
        <NutrientFacts>
          <NutrientNameId>NACL</NutrientNameId>
          <NutrientName>NACL</NutrientName>
          <NutrientPer100gUnrounded>1.000000000000000e-001</NutrientPer100gUnrounded>
          <NutrientPer100gRounded>0.12 </NutrientPer100gRounded>
        </NutrientFacts>
        <NutrientFacts>
          <NutrientNameId>ENERC_KCAL</NutrientNameId>
          <NutrientName>ENERC_KCAL</NutrientName>
          <NutrientPer100gUnrounded>1.000000000000000e+002</NutrientPer100gUnrounded>
          <NutrientPer100gRounded>100  </NutrientPer100gRounded>
        </NutrientFacts>
        <NutrientFacts>
          <NutrientNameId>ENERC_KJ</NutrientNameId>
          <NutrientName>ENERC_KJ</NutrientName>
          <NutrientPer100gUnrounded>4.200000000000000e+002</NutrientPer100gUnrounded>
          <NutrientPer100gRounded>420  </NutrientPer100gRounded>
        </NutrientFacts>
      </NutritionDetails>
      <Compliance>
        <CompliesWith>
          <CompliesWith>SQMS</CompliesWith>
        </CompliesWith>
        <CompliesWith>
          <CompliesWith>Gluten free</CompliesWith>
        </CompliesWith>
        <CompliesWith>
          <CompliesWith>Allergens Checked</CompliesWith>
        </CompliesWith>
        <CompliesWith>
          <CompliesWith>GFSI Certified</CompliesWith>
        </CompliesWith>
        <CompliesWith>
          <CompliesWith>ISO 9001 Certified</CompliesWith>
        </CompliesWith>
      </Compliance>
      <Allergens>
        <AllergensKnownToContain>
          <AllergenID>MILK (INCLUDING LACTOSE)/COW'S MILK</AllergenID>
          <AllergenName>Milk (including lactose)/Cow's milk</AllergenName>
          <AllergenMaxPer100g>9.000500000000000e+001</AllergenMaxPer100g>
        </AllergensKnownToContain>
      </Allergens>
    </ID>
  </Record>
</Records>

Gottfried Lesigang · Accepted Answer · 2018-11-28 17:17:28Z

2

For your next quesiton: Please reduce your input to the needed minimum. Best is, to create a stand-alone sample with minimal input data together with your own attempt and the expected output in order to reproduce your issue.

If I get this correctly, everything is okay, but this:

     , (
         select [CompliesWith] = cw.name
         from SpecCompliesWithJoin cwj
           join CompliesWith cw on cwj.fkCompliesWith = cw.pkid 
                               and cw.langID = 0
         where ss.SpecID = cwj.fkSpec 
         FOR XML RAW ('CompliesWith'), TYPE, ROOT('Compliance'), ELEMENTS
       )

I think you can change the last line to

         FOR XML PATH (''), TYPE, ROOT('Compliance'), ELEMENTS

Another chance is to create a nameless element with

         select [*] = cw.name

Hint: In most cases FOR XML PATH is better than RAW, AUTO or EXPLICIT...

answered Nov 28, 2018 at 17:17

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gutzy Over a year ago

That worked perfectly thank you for your help! And noted by the way about the post, moving forward I will use isolated code over the whole query.

Collectives™ on Stack Overflow

Formatting SQL Query to XML File

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related