I have two tables in SQL Database and would like to produce a simple XML
file of their schema an data. I need the output to be similar to that of a
..NET DataSet output. In a nut shell I one file that represents both tables.
An example of the desired output format is posted below. Notice that the
schema contains both tables (Errors & Temp). How would I go about writing a
SQL Procedure to perform this? (Mostly I need help with the select queries
necessary to produce the two tables for output).

Thanks,

Matt




<?xml version="1.0" standalone="yes" ?>
- <Errors>
- <xs:schema id="Errors" xmlns=""
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
- <xs:element name="Errors" msdata:IsDataSet="true">
- <xs:complexType>
- <xs:choice maxOccurs="unbounded">
- <xs:element name="Errors">
- <xs:complexType>
<xs:attribute name="ErrorNum" type="xs:int" />
<xs:attribute name="Message" type="xs:string" />
<xs:attribute name="Date" type="xs:dateTime" />
</xs:complexType>
</xs:element>
- <xs:element name="Temp">
- <xs:complexType>
<xs:attribute name="UniqueID" msdata:AutoIncrement="true"
msdata:AutoIncrementSeed="1" type="xs:int" use="required" />
<xs:attribute name="Description" type="xs:string" use="required" />
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
- <xs:unique name="Constraint1" msdata:PrimaryKey="true">
<xs:selector xpath=".//Temp" />
<xs:field xpath="@UniqueID" />
</xs:unique>
</xs:element>
</xs:schema>
<Errors ErrorNum="0" Message="Starting the Splash Screen up!"
Date="2005-01-27T09:09:12.0000000-07:00" />
</Errors>

Dts doesn't having anything like this, so I would write it in .Net if that
matches what you want.

--
Darren Green
http://www.sqldts.com
http://www.sqlis.com

"ME" <trash.trash (AT) comcast (DOT) netREMOVETHIS> wrote

In SQL Server 2005, you could probably use something like

select * from Errors for xml auto, xmlschema

To give you the data and schema...

In SQL Server 2000, we only support the older XDR format (replace xmlschema
with xmldata).

Best regards
Michael

"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote