Use XML as input parameter to SP

         Few days ago, in my blog(http://chiragrdarji.wordpress.com/2007/02/19/split-function-in-sql/)  I mentioned how to pass and use multiple values in single parameter in SP. I have mentioned that you can use XML format to do the same. Let me show you how to do that.

         Cionsider that you have to delete 5 rows from table and you have value of primary key for those 5 records. You have to prepare the XML document for these 5 IDs and pass it as a parameter to SP. Below is the functiion that generates XML.

       I have passed 5 IDs as coma seperated string in "Value", and NodeName as parameters. I will Explain NodeName later.

public static string GetOpenXMLFormat(string Value,string NodeName)
{
            string strReturnVAlue = string.Empty; 
            // Create Root node.
             strReturnVAlue = "<Root>"

            if (Value.Length > 0)
            {
                  string[] strTemp = Value.Split(new string[] {","}, 
                                                          StringSplitOptions.RemoveEmptyEntries);

                  for (int i = 0; i < strTemp.Length; i++)
                 {
                       strReturnVAlue += "<" + NodeName + "IDS " + NodeName + "ID ='"
                                                                + strTemp[i] + "'/>"

                  }
             }
            strReturnVAlue += "</Root>"
            return strReturnVAlue;
}

Fig - (1) C# code generate XML document.

 

           Lets call above function GetOpenXMLFormat("1,2,3,4,5" , "Emp"). This will generate XML file as shown below.

<Root>
         <EmpIDS EmpID = 1/>
         <EmpIDS EmpID = 2/>
         <EmpIDS EmpID = 3/>
         <EmpIDS EmpID = 4/>
         <EmpIDS EmpID = 5/>
</Root>

Fig - (2) Generated XML strig.

      Consider that we pass this parameter "@EmpIDS" to any SP. Below is the code that shows how to read this parameter in SP.

 

DELARE @EmpID int

DECLARE @docHandle int

EXEC sp_xml_preparedocument @docHandle OUTPUT, "@EmpIDS

SELECT * Into #TempEntities FROM
OPENXML(@docHandle, N'/Root/IDS',1) WITH (EmpID int)

DECLARE TempCursor CURSOR FOR

SELECT SubGroupID, TrainingModuleID FROM #TempEntities

OPEN TempCursor

/* For each row in cursor*/
FETCH NEXT FROM TempCursor INTO @EmpID

/* Loop through cursor for remaining GroupID */
WHILE @@FETCH_STATUS = 0
BEGIN

      // Wirte a code to do desire operation

END

 

Fig - (3) Ream XML string parameter in SP.

         You can pass more than one attribute in XML parameter and read it. Below is the sample for 2 values 

OPENXML(@docHandle, N'/Root/IDS',1) WITH (SubGroupID int '@SubGroupID', TrainingModuleID int '@TrainingModuleID')

Fig - (3)  Read values of 2 parameters

Happy programing.