Hi there my first post :), i've started a project that transforms a table from SQL to XML, works like a charm.Now i want to transform from a XML file to database:
1)search if there is a database created(i've done that using try catch)
if not create a database XMLReader
2)search if there is a table with the name of the xml, example:c\\projects\\Test.xml, the table should be called Test and be added in XMLReader database, imput columns and rows after reading the xml file.
The problem is at number 2, i've tryed something but i just don't know how to create a table with the fields read from the xml and populate the table.
the converted xml using .net 3.5
<?xml version="1.0" standalone="yes"?>
<NewDataSet>
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="Table">
<xs:complexType>
<xs:sequence>
<xs:element name="Id" type="xs:int" minOccurs="0" />
<xs:element name="Username" type="xs:string" minOccurs="0" />
<xs:element name="Password" type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<Table>
<Id>3</Id>
<Username>admin</Username>
<Password>gvkMJH6rRdw=</Password>
</Table>
<Table>
<Id>4</Id>
<Username>Vlad</Username>
<Password>ozjyYyibu1w=</Password>
</Table>
<Table>
<Id>5</Id>
<Username>macaroana</Username>
<Password>QRzI4TFbXUk=</Password>
</Table>
</NewDataSet>SqlConnection conn = new SqlConnection(clsSqlConn.connReader);
try
{
conn.Open();
String xml = numeXml(textBox5.Text.ToString());
ListBox ls = new ListBox();
XElement xml1 = XElement.Load(textBox5.Text.ToString());
IEnumerable<XElement> xml_query = (from c in xml1.Descendants("Table")
select c
);
int i = 0;
ArrayList name = new ArrayList();
ArrayList value = new ArrayList();
ArrayList type = new ArrayList();
foreach (XElement x in xml_query)
{
for (int j = 0; j < name.Count; j++)
{
if (name[j] == x.Elements().ToArray()[i].Name.LocalName)
name.Add(x.Elements().ToArray()[i].Name.LocalName);
}
for (int z = 0; z < value.Count; z++)
{
if(value[z] == x.Elements().ToArray()[z].Value)
value.Add(x.Elements().ToArray()[z].Value);
}
//don't know if it works well, i would need to get the columns type needed when creating the table
type.Add(x.GetType().GetElementType().ToString());
// MessageBox.Show(x.Elements().ToArray()[i].Name.LocalName);
// MessageBox.Show(x.Elements().ToArray()[i].Value);
i++;
// break;
}
try
{
//insert into table if already on the database if not go to catch and create the table and insert in there
}
catch
{
string sql = "CREATE TABLE " + xml + " (" + name[0].ToString() + "INT NOT NULL AUTO_INCREMENT, PRIMARY KEY("+name[0].ToString()+"),";
;
for(int j=1; j<name.Count; j++)
{
sql += name[j].ToString() + " "+ type[j].ToString()+",";
}
sql += ")";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
/*comanda = INSERT INTO xml ("...
for(int j=0;j<name.Count;j++)
{
comanda += value[j]+","
}
*/
}
}
catch
{
String str;
SqlConnection myConn = new SqlConnection(@"Data Source=server;Integrated security=SSPI;database=master");
str = @"CREATE DATABASE XMLReader ON PRIMARY " +
"(NAME = XMLReader, " +
"FILENAME = 'C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\DATA\\XMLReader.mdf', " +
"SIZE = 2MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " +
"LOG ON (NAME = MyDatabase_Log, " +
"FILENAME = 'C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\DATA\\XMLReader.ldf', " +
"SIZE = 1MB, " +
"MAXSIZE = 20MB, " +
"FILEGROWTH = 10%)";
SqlCommand cmd = new SqlCommand(str, myConn);
try
{
myConn.Open();
cmd.ExecuteNonQuery();
}
finally
{
myConn.Close();
}
}
finally
{
conn.Close();
}just wanted to know if u know another solution or if i'm on the right track.
