Generate query results in XML format and save them to an XML text

SQL statement for generating query results in XML format:

Select ID, username, userpwd from userinfo for XML auto, Elements

The following code shows how to save the query results in XML format to a file named userinfo. XML in vs2005. Of course, we must first create an XML file named userinfo. xml.

The Code is as follows:

Sqlconnection conn = new sqlconnection ();

Conn. connectionstring = configurationmanager. connectionstrings ["connstring"]. connectionstring;

Sqlcommand cmd = conn. createcommand ();

Cmd. commandtext = "select ID, username, userpwd from userinfo for XML auto, elements"; // query SQL statements in XML format

Conn. open ();

Xmlreader XRD = cmd. executexmlreader (); // use the executexmlreader method to generate an xmlreader object.

Xmldocument xmldom = new xmldocument (); // create an xmldocument object xmldom

Xmldeclaration declaration = xmldom. createxmldeclaration ("1.0", "UTF-8", null); // declare the version of the XML object, encoding method

Xmldom. appendchild (Declaration); // Add the Declaration node to xmldom

Xmlelement root = xmldom. createelement ("root"); // Add a root node root

Xmldom. appendchild (Root); // Add the root node to xmldom. Because each XML file must have a root node, you must add

Xmlelement node = NULL; // create a subnode under the root account

Xmlelement child = NULL; // create a subnode under a node

Xmltext text = NULL; // create a text node, such as <ID> 1 </ID>. The one in the middle is also a node, which is a node of the xmltextnode type.

While (XRD. Read () // read nodes one by one
...{
If (XRD. nodetype = xmlnodetype. element) // If the node type is the Start Element type, element is the Start Element type such as <ID>, and endelement is the end node type such as </ID>
...{
If (XRD. Name. Equals ("userinfo") // If the node element is named userinfo
...{
Node = xmldom. createelement (XRD. Name); // instantiate a node element named XRD. Name

Root. appendchild (node); // Add the child node to the root node
}
Else // If the node element name is not userinfo, It is ID, username, or userpwd
...{
Child = xmldom. createelement (XRD. Name );

Node. appendchild (child); // Add the node to the subnode of the node
}
}

If (XRD. nodetype = xmlnodetype. Text) // If the node type is text, that is, the text value between the Start Node and the End Node
...{
TEXT = xmldom. createtextnode (XRD. value );

Child. appendchild (text); // instantiate a text node and add it to the child node
}
}

Xmldom. Save (server. mappath ("userinfo. xml"); // Save the content of xmldom to userinfo. xml

XRD. Close (); // close the xmlreader object. Note: Close the xmlreader object after it is used up. The principle is the same as that of the sqldatareader object because it accesses the database link exclusively.

// Release resources related to database operations
Conn. Close ();

Conn. Dispose ();

Cmd. Dispose ();

Do not forget to add the namespace:

Using system. xml;
Using system. Data. sqlclient;
Using system. text;