CODEWITHSUNDEEP
c#sql-serverxmllinq-to-xml
yangl
yangl

Reputation: 337

about save xdocument to sql server xml column

here is my xml file:

<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns1="http://mfisoft.ru/soap" xmlns:ns2="http://xml.apache.org/xml-soap" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
  <SOAP-ENV:Body>
    <ns1:selectRowsetResponse>
      <result SOAP-ENC:arrayType="ns2:Map[1]" xsi:type="SOAP-ENC:Array">
        <item xsi:type="ns2:Map">
          <item>
            <key xsi:type="xsd:string">report_id</key>
            <value xsi:type="xsd:string">246</value>
          </item>
        </item>
      </result>
    </ns1:selectRowsetResponse>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

and this is my table in sql server 

CREATE TABLE [dbo].[HowToSaveXML](
    [ID] [int] NOT NULL,
    [XMLcontent] [xml] NOT NULL
)

when i write such sql command in sql server management studio

insert into HowToSaveXML values (5,'<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns1="http://mfisoft.ru/soap" xmlns:ns2="http://xml.apache.org/xml-soap" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
  <SOAP-ENV:Body>
    <ns1:selectRowsetResponse>
      <result SOAP-ENC:arrayType="ns2:Map[1]" xsi:type="SOAP-ENC:Array">
        <item xsi:type="ns2:Map">
          <item>
            <key xsi:type="xsd:string">report_id</key>
            <value xsi:type="xsd:string">246</value>
          </item>
        </item>
      </result>
    </ns1:selectRowsetResponse>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>')

it works fine and successfully add one record into table HowToSaveXML

but when i using c# code to do the same things , like this:

    XDocument xd = XDocument.Load(@"D:\temp\0919\sDocumentToXML.xml");

    sqlCmdUpdate = new SqlCommand("insert into  HowToSaveXML values ( 5 , " + xd.ToString(), conn);

    sqlCmdUpdate.CommandTimeout = 200;
    if (conn.State == ConnectionState.Closed)
        conn.Open();
    try
    {
        sqlCmdUpdate.ExecuteNonQuery();
    }
    catch (Exception aep)
    {

    }

where sDocumentToXML.xml contain the same content with above file ,but it throw a Exception :

Incorrect syntax near '<'.
The label 'xmlns' has already been declared. Label names must be unique within a query batch or stored procedure

it seems system does not allow duplicate node , but i have successfully insert the same xml by using insert command directly in management studio, so what is the reason, how can i using c# code to do the same thing? thanks.

plus : if i change c# code like this 

    sqlCmdUpdate = new SqlCommand("insert into  HowToSaveXML values ( 5 , '" + xd.ToString() + "'", conn);

it also throw a exception.

Upvotes: 1

Views: 2106

Answers (1)

Knaģis
Knaģis

Reputation: 21485

For sending string values to the SQL you always need to use parameters, instead of building the query as string.

sqlCmdUpdate = new SqlCommand("insert into HowToSaveXML values (5 , @xml)", conn);
sqlCmdUpdate.Parameters.AddWithValue("@xml", xd.ToString());

Documentation: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection.addwithvalue.aspx

Upvotes: 3

Related Questions