Link to home
Create AccountLog in
Avatar of PetEdge
PetEdgeFlag for Afghanistan

asked on

Generate differently formatted XML file from SQL statement

My input data looks like this:
Table: t1
Item       Offer
ab123   O1234
ab235   O3265

My goal is to build an XML file, using SQL, which looks like this:
<Items>
  <Item>
    <Style>DT130 48                 </Style>
    <SourceCodeList>
      <SourceCode>708YDRP3</SourceCode>
    </SourceCodeList>
  </Item>

Here's the code I am using in SQL:
SELECT [item] as "Style",
       (select offer as "SourceCode" from t1 t
where t.[item] = c.[item]  for xml path (''), type) as "SourceCodeList"
FROM   (select distinct [item] from t1) c
FOR XML PATH ('Item'), type, elements, Root ('Items')

Unfortunately, the code generates a file with an extra element with each record (<Item />:  
<Items>
  <Item />
  <Item>
    <Style>DT130 48                 </Style>
    <SourceCodeList>
      <SourceCode>708YDRP3</SourceCode>
    </SourceCodeList>
  </Item>

Please advise how to remove.  
Alternately -- My preference would be to build the ouptut file using SSIS.  Advice for how to do this would be appreciated!  Thanks!
ASKER CERTIFIED SOLUTION
Avatar of brejk
brejk
Flag of Poland image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of PetEdge

ASKER

That did the trick.  Thanks!