How to pass XML from C# to a stored procedure in SQL Server 2008?
Solution 1
For part 2 of your question, see my answer to Stored procedure: pass XML as an argument and INSERT (key/value pairs) for an example of how to use XML within a stored procedure.
EDIT: Sample code below is based on the specific example given in the comments.
declare @MyXML xml
set @MyXML = '<booksdetail>
<isbn_13>700001048</isbn_13>
<isbn_10>01048B</isbn_10>
<Image_URL>http://www.landt.com/Books/large/00/70100048.jpg</Image_URL>
<title>QUICK AND FLUPKE</title>
<Description> PRANKS AND JOKES QUICK AND FLUPKE - CATASTROPHE QUICK AND FLUPKE </Description>
</booksdetail>'
select Book.detail.value('(isbn_13/text())[1]','varchar(100)') as isbn_13,
Book.detail.value('(isbn_10/text())[1]','varchar(100)') as isbn_10,
Book.detail.value('(Image_URL/text())[1]','varchar(100)') as Image_URL,
Book.detail.value('(title/text())[1]','varchar(100)') as title,
Book.detail.value('(Description/text())[1]','varchar(100)') as Description
from @MyXML.nodes('/booksdetail') as Book(detail)
Solution 2
As stated in http://support.microsoft.com/kb/555266, you need to pass xml data as NText.
You can query an XML variable as follows:
DECLARE @PeopleXml XML
SET @PeopleXml = '<People>
<Person>
<Name>James</Name>
<Age>28</Age>
</Person>
<Person>
<Name>Jane</Name>
<Age>24</Age>
</Person>
</People>'
-- put [1] at the end to ensure the path expression returns a singleton.
SELECT p.c.value('Person[1]/Name[1]', 'varchar(50)')
FROM @PeopleXml.nodes('People') p(c) -- table and column aliases
Solution 3
public static string UpdateStaticCertificateFormateNo1Data(StaticCertificateFormatNo1LogicLayer StaticFormat1Detail)
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
con.Open();
string strXMLRegistrationDetails, strXMLQutPut = "<root></root>";
System.Xml.Serialization.XmlSerializer x = new System.Xml.Serialization.XmlSerializer(StaticFormat1Detail.GetType());
System.IO.MemoryStream stream = new System.IO.MemoryStream();
x.Serialize(stream, StaticFormat1Detail);
stream.Position = 0;
XmlDocument xd = new XmlDocument();
xd.Load(stream);
strXMLRegistrationDetails = xd.InnerXml;
SqlTransaction trn = con.BeginTransaction();
try
{
SqlParameter[] paramsToStore = new SqlParameter[2];
paramsToStore[0] = ControllersHelper.GetSqlParameter("@StaticFormat1Detail", strXMLRegistrationDetails, SqlDbType.VarChar);
paramsToStore[1] = ControllersHelper.GetSqlParameter("@OutPut", strXMLQutPut, SqlDbType.VarChar);
SqlHelper.ExecuteNonQuery(trn, CommandType.StoredProcedure, "UPS_UpdateStaticCertificateFormateNo1Detail", paramsToStore);
trn.Commit();
}
catch (Exception ex)
{
trn.Rollback();
con.Close();
if (ex.Message.Contains("UNIQUE KEY constrastring"))
{ return "Details already in List"; }
else { return ex.Message; }
}
con.Close();
return "Details successfully Added...";
}
Geeth
Updated on July 24, 2020Comments
-
Geeth almost 4 years
I want to pass xml document to sql server stored procedure such as this:
CREATE PROCEDURE BookDetails_Insert (@xml xml)
I want compare some field data with other table data and if it is matching that records has to inserted in to the table.
Requirements:
How do I pass XML to the stored procedure? I tried this, but it doesn’t work:[Working]
command.Parameters.Add( new SqlParameter("@xml", SqlDbType.Xml) { Value = new SqlXml(new XmlTextReader(xmlToSave.InnerXml, XmlNodeType.Document, null)) });
How do I access the XML data within the stored procedure?
Edit: [Working]
String sql = "BookDetails_Insert"; XmlDocument xmlToSave = new XmlDocument(); xmlToSave.Load("C:\\Documents and Settings\\Desktop\\XML_Report\\Books_1.xml"); SqlConnection sqlCon = new SqlConnection("..."); using (DbCommand command = sqlCon.CreateCommand()) { **command.CommandType = CommandType.StoredProcedure;** command.CommandText = sql; command.Parameters.Add( new SqlParameter("@xml", SqlDbType.Xml) { Value = new SqlXml(new XmlTextReader(xmlToSave.InnerXml , XmlNodeType.Document, null)) }); sqlCon.Open(); DbTransaction trans = sqlCon.BeginTransaction(); command.Transaction = trans; try { command.ExecuteNonQuery(); trans.Commit(); sqlCon.Close(); } catch (Exception) { trans.Rollback(); sqlCon.Close(); throw; }
Edit 2: How to create a select query to select pages, description based on some conditions.
<booksdetail> <isn_13>700001048</isbn_13> <isn_10>01048B</isbn_10> <Image_URL>http://www.landt.com/Books/large/00/7010000048.jpg</Image_URL> <title>QUICK AND FLUPKE</title> <Description> PRANKS AND JOKES QUICK AND FLUPKE </Description> </booksdetail>