Generating xml from a database

[BrilloPad]

Any good tools for generating xml getting data from a database?

This is SQL Server. I am looking at using tsql commands.

It is clearly doable - though sqlserver has some interesting considerations(duplicate rows etc.)

SSIS seems to have nothing useful.

[DimPrawn]

Does the XML need to conform to an XSD schema?

Or can it be any XML, as long as it's valid XML?

Because if it's just any XML, simply put

for xml auto

on the end of any queries and then save the string.

[BrilloPad]

Does the XML need to conform to an XSD schema?

Or can it be any XML, as long as it's valid XML?

Because if it's just any XML, simply put

for xml auto

on the end of any queries and then save the string.

Cheers for the response.

It does need to correspond to a schema.

Formatting the sql select to the xml string required is a huge pain. I thought there might be some tools out there to help - though maybe this is a niche requirement.

Plan C maybe.....

[geoffreywhereveryoumaybe]

XML to Database

Any idea about tools or software to do the reverse transformation?

[DimPrawn]

Cheers for the response.

It does need to correspond to a schema.

Formatting the sql select to the xml string required is a huge pain. I thought there might be some tools out there to help - though maybe this is a niche requirement.

Plan C maybe.....

Yes, the T-SQL to force into into the required schema is excruitating....there are a few solutions, none of them I would call painless though.

1. Export as raw XML from the SQL Server and then use C# to apply an XSLT tranform into the required XML schema.
2. Read the data in C# using ADO.NET or EF or whatever and then generate the XML document from the data in C# code.
3. Use SSIS to apply an XSLT

[minestrone]

can you not structure the select in the query to concatenate the XML elements as strings together with the columns?

Not pretty obviously.

EDIT: sorry seen you probably mentioned that as being a pain.

[LondonManc]

Any good tools for generating xml getting data from a database?

This is SQL Server. I am looking at using tsql commands.

It is clearly doable - though sqlserver has some interesting considerations(duplicate rows etc.)

SSIS seems to have nothing useful.

https://msdn.microsoft.com/en-GB/library/ms178107.aspx