Generating XML Schema from SQL Server table

[Lavanya]

Hi,

I want to generate a xml schema from SQL server table(test) in this format.

<?xml version="1.0">
<schema name="test" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">
<Attribute name="attRequired" required="yes"/>
<Attribute name="attDefaultZero" default="0"/>
<ElementType name="tes_1" dt:type="e2" dt:min="1" dt:max="15"/>
<description>test1</description>
<ElementType name="tes_2" dt:type="e2"/>
<description>test2</description>
<ElementType name="tes_3" dt:type="eltonly">
<Element type="tes_1">
<attribute type="attRequired"/>
</Element>
<Element type="tes_2">
<attribute type="attRequired"/>
</Element>
</ElementType>
</schema>

How do i do that? please help me.
Can it be accomplished by writing a stored procedure??
Regards,
Lavanya

[Rohit Wason]

"Lavanya" <lavss@hotmail.com> wrote:
>
>Hi,
>
>I want to generate a xml schema from SQL server table(test) in this format.
>
><?xml version="1.0">
><schema name="test" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">
><Attribute name="attRequired" required="yes"/>
><Attribute name="attDefaultZero" default="0"/>
><ElementType name="tes_1" dt:type="e2" dt:min="1" dt:max="15"/>
><description>test1</description>
><ElementType name="tes_2" dt:type="e2"/>
><description>test2</description>
><ElementType name="tes_3" dt:type="eltonly">
><Element type="tes_1">
><attribute type="attRequired"/>
></Element>
><Element type="tes_2">
><attribute type="attRequired"/>
></Element>
></ElementType>
></schema>
>
>How do i do that? please help me.
>Can it be accomplished by writing a stored procedure??
>Regards,
>Lavanya

It can't be done with SQL server - as you can't generate files from a database
server (even if you can, it'll be generated on your SQL server m/c).

So, you need a component (a program written in VB/VC etc), which runs on
the PC, where you want to make the XML file, and using some data access method
(ADO/RDO etc.), retrieve the Recordset you want to populate, write it to
a file (using VB Write/ FileSystemObject component) and save it. That's it

bye
Rohit