PetEdge
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</Sour ceCode>
</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</Sour ceCode>
</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!
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</Sour
</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</Sour
</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
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER