CODEWITHSUNDEEP
sql-serverxmlssis
Bob Clegg
Bob Clegg

Reputation: 573

SqlServer SSIS XML Export possibility

I need to export XML data from a Sql Server table using a known schema. The schema takes the form 

<myField>value</myField>

As far as I can make out I need to use the FOR XML clause in the select statement. All options for that clause seem to generate XML in the form of:

<element myField=value /element>

i.e. Data is inserted as attributes of elements instead of being values of elements.

It seems that the best way to deal with this need is to handle the XML file generation inside of an application.

Am I correct in this conclusion or is it lack of understanding of TSQL xml capabilities on my part?

thanks Bob

Upvotes: 0

Views: 76

Answers (1)

TTeeple
TTeeple

Reputation: 2989

I don't know if you can get the exact format you are looking for but this comes pretty close. The Point and DateTimeFormat column will be repeated for each row.

SELECT Point
,DateTimeFormat
,DT AS "S/DT"
,V AS "S/V"
,Q AS "S/Q"
FROM mytable
FOR XML PATH(''), TYPE, ELEMENTS, ROOT('Data')

Check out the SQL Fiddle

Upvotes: 1

Related Questions