SQL Server Returns XML format data

Let's look at the example. Suppose there is only one users table with two fields: ID and PWD.

1. The simplest usage:

select * from users for xml auto

The result is as follows:

<users id="walker" pwd="walker" /><users id="walker" pwd="1C3A0B25C3D1C909E2BCB9FE44C2F904" /><users id="walker2" pwd="1C3A0B25C3D1C909E2BCB9FE44C2F904" /><users id="walker3" pwd="1C3A0B25C3D1C909E2BCB9FE44C2F904" /><users id="walker4" pwd="1C3A0B25C3D1C909E2BCB9FE44C2F904" /><users id="walker5" pwd="1C3A0B25C3D1C909E2BCB9FE44C2F904" /><users id="john" pwd="insert time:2010/09/06 11:50:40" /><users id="steph" pwd="insert time:2010/09/06 11:50:40" /><users id="john" pwd="insert time:2010/09/06 11:50:53" /><users id="steph" pwd="insert time:2010/09/06 11:50:53" /><users id="john" pwd="insert time:2010/09/06 11:51:01" /><users id="steph" pwd="insert time:2010/09/06 11:51:01" />

2. The method for generating a node for each data row (Note: The default name of each node is row and can be replaced by a custom name, which is set to users here)

Select * from users for XML raw ('users') -- if you do not add ('users'), the node name is row.

Because I set the node name to users, the generated XML data is exactly the same as that in the previous example. Otherwise, only the node names are different.

3. Path usage:

select id "user/id", pwd "user/pwd" from users for xml path

Result: Column 1 and 2 are all attributes, and the path uses the rule to change the column to a node. By the way, I demonstrated how to customize the hierarchical relationship.

<row>  <user>    <id>walker</id>    <pwd>walker</pwd>  </user></row><row>  <user>    <id>walker</id>    <pwd>1C3A0B25C3D1C909E2BCB9FE44C2F904</pwd>  </user></row><row>

4. The usage of explicit has not been studied yet...

 

I used the following code to read XML data:

String SQL = "select * from users for XML auto"; // The result is displayed in XML format: sqldatabase DB = databasefactory. createdatabase () as sqldatabase; ienumerable <string> result = NULL; dbcommand cmd = dB. getsqlstringcommand (SQL); Using (VAR reader = dB. executexmlreader (CMD) {If (reader. isstartelement () {var root = (xelement) xnode. readfrom (Reader); // LINQ to XML usage result = root. elements ("users "). attributes ("ID "). select (x => X. value);} return result ;}

Root. Elements ("users"). attributes ("ID ")... If the value is not obtained, you can use the XML document for testing. If you find that the value can be normal, you may think that there is no root node. (C # can be understood as multiple root nodes, because each node name is the same ),

After testing, How can I change the query statement? Add a root ('yourname') to all the preceding query statements, for example:

select * from users for xml auto , root ('info')

Result:

<info>  <users id="walker" pwd="walker" />  <users id="walker" pwd="1C3A0B25C3D1C909E2BCB9FE44C2F904" />  <users id="walker2" pwd="1C3A0B25C3D1C909E2BCB9FE44C2F904" />  <users id="walker3" pwd="1C3A0B25C3D1C909E2BCB9FE44C2F904" />  <users id="walker4" pwd="1C3A0B25C3D1C909E2BCB9FE44C2F904" />  <users id="walker5" pwd="1C3A0B25C3D1C909E2BCB9FE44C2F904" />  <users id="john" pwd="insert time:2010/09/06 11:50:40" />  <users id="steph" pwd="insert time:2010/09/06 11:50:40" />  <users id="john" pwd="insert time:2010/09/06 11:50:53" />  <users id="steph" pwd="insert time:2010/09/06 11:50:53" />  <users id="john" pwd="insert time:2010/09/06 11:51:01" />  <users id="steph" pwd="insert time:2010/09/06 11:51:01" /></info>