1: If the parameter is of the int type:
Declare @ a xml
Set @ a ='
<Row> <id> 1 </id> </row>
<Row> <id> 5 </id> </row>
<Row> <id> 4 </id> </row>
<Row> <id> 3 </id> </row>
<Row> <id> 2 </id> </row>'
Select * from product where id in (
Select d. x. value ('./id [1]', 'int') from @ a. nodes ('/*') as d (x ))
2: If the parameter is of the varchar type:
Declare @ a xml
Set @ a ='
<Row> <name> a </name> </row>
<Row> <name> b5 </name> </row>
<Row> <name> c4 </name> </row>
<Row> <name> d3 </name> </row>
<Row> <name> e2 </name> </row>'
Select * from product where pname in (
Select d. x. value ('./name [1]', 'varchar (100) ') from @ a. nodes ('/* ') as d (x ))
C # simple method written in the first example
Public DataSet GetData (List <int> idList) {System. text. stringBuilder idXML = new System. text. stringBuilder (); // convert IdList to idxml (the value of the xml parameter to be used later) foreach (var item in idList) {idXML. appendFormat ("<row> <id> {0} </id> </row>", item);} System. text. stringBuilder strSql = new System. text. stringBuilder (); strSql. append ("select * from product where id in ("); // parse the xml parameter @ a to retrieve the ID. Here @ a is considered as a special table strSql. append ("select d. x. value ('. /id [1] ', 'int') from @. nodes ('/*') as d (x) "); strSql. append (")"); SqlConnection con = new SqlConnection ("database connection string"); SqlCommand cmd = new SqlCommand (strSql. toString (), con); // The parameter value is SqlParameter [] para = new SqlParameter [] {new SqlParameter ("@ a", SqlDbType. xml) {Value = idXML. toString () }}; cmd. parameters = para; // query SqlDataAdapter sda = new SqlDataAdapter (cmd); DataSet ds = new DataSet (); sda. fill (ds); return ds ;}
If you think it is a good article, please do not mean your suggestion. It is not easy to write an article.