How to convert from sql to xml

[Tuna Jen]

Hi All

I have a requirement to convert the data from sql to xml using java.

For Example If i write a query like this....

"SELECT TABLE_NAME FROM INFORMATION_SCHEMA.tables WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME ike 'tbl_sis_%'"

I will be getting following two xml and two xsd files....

BASE_TABLE.xml
tbl_sis_products.xml

BASE_TABLE.xsd
tbl_sis_products.xsd

It would be great help if some one gives some idea on it.

Thanks
Tuna

[Ulf Dittmer]

What should be the contents of those XML and XSD files?

[Tuna Jen]

Hi Ulf Dittmer

Thanks for replying

Following sample contents for BASE_TABLE.xml and tbl_sis_products.xml .Sent for ref purpose

<?xml version="1.0" standalone="yes"?>
<iPos_v1>
<INFORMATION_SCHEMA>
<TABLE_NAME>assets_liabilities</TABLE_NAME>
</INFORMATION_SCHEMA>
<INFORMATION_SCHEMA>
<TABLE_NAME>budget</TABLE_NAME>
</INFORMATION_SCHEMA>
<INFORMATION_SCHEMA>
<TABLE_NAME>client_insurance_plans</TABLE_NAME>
</INFORMATION_SCHEMA>
<INFORMATION_SCHEMA>
<TABLE_NAME>client_remarks</TABLE_NAME>
</INFORMATION_SCHEMA>
<INFORMATION_SCHEMA>
<TABLE_NAME>contact_address</TABLE_NAME>
</INFORMATION_SCHEMA>
</<iPos_v1>>
-----------------------------------------------

<?xml version="1.0" standalone="yes"?>
<iPos_v1>
<tbl_sis_products>
<product_id>1</product_id>
<product_code>LIP</product_code>
<product_name>Life Ins</product_name>
<product_description>Life Insurance Plan</product_description>
<is_active>true</is_active>
</tbl_sis_products>
</iPos_v1>
----------------------------------------------------
Following two files for BASE_TABLE.xsd and tbl_sis_products.xsd respectively

<?xml version="1.0" standalone="yes"?>
<xs:schema id="iPos_v1" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="iPos_v1" msdata:IsDataSet="true" msdata:MainDataTable="INFORMATION_SCHEMA" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="INFORMATION_SCHEMA">
<xs:complexType>
<xs:sequence>
<xs:element name="TABLE_NAME" type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
------------------------------------------
<?xml version="1.0" standalone="yes"?>
<xs:schema id="iPos_v1" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="iPos_v1" msdata:IsDataSet="true" msdata:MainDataTable="tbl_sis_products" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="tbl_sis_products">
<xs:complexType>
<xs:sequence>
<xs:element name="product_id" type="xs:int" minOccurs="0" />
<xs:element name="product_code" type="xs:string" minOccurs="0" />
<xs:element name="product_name" type="xs:string" minOccurs="0" />
<xs:element name="product_description" type="xs:string" minOccurs="0" />
<xs:element name="is_active" type="xs:boolean" minOccurs="0" />
<xs:element name="display_order" type="xs:int" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
-------------------------------------------