Write XML documents to the database

1. Before the drill, prepare an XML file firstxml. xml and firstxml. XSD, and the two files are under the root directory. Their contents are as follows:

Firstxml. xml
{
<? XML version = "1.0" standalone = "yes"?>
<Newdataset>
<Table>
<ID> 1 </ID>
<Text> AAA </text>
<Belong> 0 </belong>
</Table>
<Table>
<ID> 2 </ID>
<Text> BBB </text>
<Belong> 0 </belong>
</Table>
<Table>
<ID> 3 </ID>
<Text> CCC </text>
<Belong> 0 </belong>
</Table>
</Newdataset>
}
Firstxml. XSD
{
<? XML version = "1.0" encoding = "UTF-8"?>
<! -- <Autogenerated>
This code was generated by a tool to store the Dataset Designer's layout information.
Changes to this file may cause incorrect behavior and will be lost if
The code is regenerated.
</Autogenerated> -->
<Diagramlayout xmlns: xsi = "http://www.w3.org/2001/XMLSchema-instance
"Xmlns: XSD =" http://www.w3.org/2001/XMLSchema
"
Ex: showrelationlabel = "false" viewportx = "0" viewporty = "0"
Xmlns: Ex = "urn: Schemas-Microsoft-com: XML-msdatasource-layout-extended"
Xmlns = "urn: Schemas-Microsoft-com: XML-msdatasource-layout">
<Shapes>
<Shape id = "designtable: Table" zorder = "1" x = "70" Y = "70"
Height = "79" width = "150" adapterexpanded = "true" datatableexpanded = "true"
Oldadapterheight = "0" olddatatableheight = "22" splitterposition = "75"/>
</Shapes>
<Connectors/>
</Diagramlayout>
}
2. The following code writes the preceding XML document to the database.
Public void readxmltosavetotablemethordone ()
{
Sqlconnection conn = new sqlconnection ("Server = David; database = test; uid = sa; Pwd = ");
Conn. open ();
Xmldocument XD = new xmldocument ();
XD. Load (server. mappath ("~ // ") +" Firstxml. xml ");
Xmlnodelist xn = XD. selectnodes ("newdataset/table ");
String text;
Int belong;
For (INT I = 0; I <XN. Count; I ++)
{
Xmlnode id = xn [I]. selectsinglenode ("ID ");
TEXT = xn [I]. selectsinglenode ("text"). innertext;
Belong = convert. toint32 (XN [I]. selectsinglenode ("Belong"). innertext );
Response. Write (Id. innertext. tostring () + "|" + TEXT + "|" + belong + "<br> ");
Sqlcommand comm = new sqlcommand ("insert into tree (text, belong) values ('" + TEXT + "'," + belong + ")", Conn );
Comm. executenonquery ();
}
Conn. Close ();
}

You can call this method when necessary to write the content in the XML document to the database.

3. You can also read the XML document into dataset. if you add a gridview (vs2005) or gridview (vs2003), you can display the data in dataset. The Code is as follows:
{Public void show ()
{
Readtabletoxml ();
Dataset DS = new dataset ();
String pathxml = server. mappath ("~ // ") +" Firstxml. xml ";
String pathxsd = server. mappath ("~ // ") +" Firstxml. XSD ";
DS. readxmlschema (pathxsd );
DS. readxml (pathxml );
This. gridview1.datasource = Ds. Tables ["table"];
This. gridview1.databind ();
}

4. If the XML document is directly written to the database in 2, someone will ask if you can read the XML document into dataset first, and then update the content in dataset to the number.
Database
Medium? The answer is yes. let's take the preceding XML document as an example. Before proceeding, we should first create a database with the same data structure as the XML document. in fact, this is also true in 2. this database
In test, the data table used is the tree. The code below implements the above functions.
Public void readxmltosavetotablemethordone ()
{
Sqlconnection conn = new sqlconnection ("Server = David; database = test; uid = sa; Pwd = ");
Sqldataadapter da = new sqldataadapter ("select * from tree where id =-1", Conn );
Sqlcommandbuilder cbuider = new sqlcommandbuilder (DA );
Dataset DS = new dataset ();
Da. Fill (DS );
String pathxml = server. mappath ("~ // ") +" Firstxml. xml ";
String pathxsd = server. mappath ("~ // ") +" Firstxml. XSD ";
DS. readxmlschema (pathxsd );
DS. readxml (pathxml );
Da. Update (DS );
Response. Write ("okokokokok ");
}

At this point, you will find that there are two solutions to write an XML document into the database, one is to directly write the XML document into the database, such as 2, the other is to read the XML document first to dataset, and then write the content in dataset to the database.