To create table in SQL by reading the values from XML file

  • Thread starter Thread starter Priya
  • Start date Start date
P

Priya

Hey all,

Is there a way to create a table in the SQL database by using an XML
file as an input? I want to have an XML file which holds all the
details of the table like the tablename, columns, datatypes, table
constraints etc.. This XML file should be read and parsed to create a
table in SQL according to the specification provided in the XML file.
I am not sure how to proceed with this. Any help would be appreciated.
 
Access does this right out of the box

you can do this for Access MDB or Access ADP (100% SQL Server)

RIGHT-CLICK IMPORT
 
Hey all,

Is there a way to create a table in the SQL database by using an XML
file as an input? I want to have an XML file which holds all the
details of the table like the tablename, columns, datatypes, table
constraints etc.. This XML file should be read and parsed to create a
table in SQL according to the specification provided in the XML file.
I am not sure how to proceed with this. Any help would be appreciated.
Click to expand...

It's definitely possible, the two parts you need to figure out is how
to write the Create Table Sql statement and then how to parse the xml
file into that. I'm afraid I can't be too much help on the query side,
but everything you need for the Xml parsing resides in the System.Xml
namespace. Perhaps you could provide a short version of the Xml file
if you need some help parsing it. By the which database are you using?
I'm guessing Sql server?

Thanks,

Seth Rowe
 
Priya said:
Hey all,

Is there a way to create a table in the SQL database by using an XML
file as an input? I want to have an XML file which holds all the
details of the table like the tablename, columns, datatypes, table
constraints etc.. This XML file should be read and parsed to create a
table in SQL according to the specification provided in the XML file.
I am not sure how to proceed with this. Any help would be appreciated.
Click to expand...


Using the following XML as a basis for discussion:

<?xml version="1.0" encoding="utf-8" ?>
<Customers>
<Customer>
<Name>Me</Name>
<Address>There</Address>
<Age>21</Age>
</Customer>
<Customer>
<Name>Me with A Very Big Name Eh (cause I'm Canadian)</Name>
<Address>There is another long address since Canada is a big
country</Address>
<Age>N/A</Age>
<Degree>BSC</Degree>
</Customer>
</Customers>


Now with the above XML it can be inferred that the table name would be
Customers and each Customer(s) row would have Name, Address and Age fields.
You can find the node names using the XML namespace in Dot.Net.

The interesting part comes now.

Each "row" would have to be read and for each "column" both the type and max
length of the data would need to be determined. Notice that in the first
"row" Age is numeric but in the last it is a string. Those are the types of
things you will have to deal with.

Another case is in the last "row" there is a Degree field that does not
exist in other places so you would need to create a column for that with a
default value or null for all rows with out this value.

Good luck

Lloyd Sheen
 
It's definitely possible, the two parts you need to figure out is how
to write the Create Table Sql statement and then how to parse the xml
file into that. I'm afraid I can't be too much help on the query side,
but everything you need for the Xml parsing resides in the System.Xml
namespace. Perhaps you could provide a short version of the Xml file
if you need some help parsing it. By the which database are you using?
I'm guessing Sql server?

Thanks,

Seth Rowe
Click to expand...

I dont have any particular database specification. I have an interface
which can connect to any database. So I want this XML parsing also to
be common. My XML spec
<Tables>
<Table Name="Employee">
<Fields>
<Field Name="EmployeeID">
<Datatype>Varchar</Datatype>
<Length>30</Length>
<AllowNull>False</AllowNull>
<PrimaryKey>PK_EmployeeID</PrimaryKey>
</Field>
<Field Name="EmployeeName">
...........
</Field>
</Fields>
<TableConstraints>
<ForeignKey Name="FK_Name">
<RefTable>TableName</RefTable>
<RefColumn>ColumnName</RefColumn>
<FieldName>ColumnName</FieldName>
</ForeignKey>
</TableConstraints>
</Table>
</Tables>
I need to parse an XML file similar to this and then create the table
using the interface that has the create table method. Should I use the
XML node and Child node concepts in the System.XML namespace to do
this. Or is there a better method to parse the XML file?

Thanks
 
Back
Top