I'm getting a weird error when I pass Xml to a stored procedure in .net. I
have an object that I serialize to Xml, then try to send the resulting text
to a stored procedure. Everything runs fine if I call the SP in Query
Analyzer, but when I call it in .Net I get the following error: "XML parsing
error: Invalid at the top level of the document.". The error occurs when I
call sp_xml_preparedocument.
Any ideas what could be wrong? Code snippets and XML doc is below
calling the SP:
string strXml = SerializationHelper.SerializeToXml(this); //just turns the
object into an XML doc.
SqlCommand cmdSave = new SqlCommand("sp_SaveCompany", conn);
cmdSave.CommandType = CommandType.StoredProcedure;
cmdSave.Parameters.Add(new SqlParameter("@sxml", SqlDbType.Text));
cmdSave.Parameters["@sxml"].Value = strXml;
cmdSave.Parameters["@sxml"].Size = strXml.Length;
cmdSave.ExecuteNonQuery();
The Exception I get:
{"XML parsing error: Invalid at the top level of the document." }
[System.Data.SqlClient.SqlException]: {System.Data.SqlClient.SqlException}
System.Object: {System.Data.SqlClient.SqlException}
_COMPlusExceptionCode: -532459699
_className: null
_exceptionMethod: null
_exceptionMethodString: null
_message: "System error."
_innerException: null
_helpURL: null
_stackTrace: {System.Array}
_stackTraceString: null
_remoteStackTraceString: null
_remoteStackIndex: 0
_HResult: -2146232060
_source: null
_xptrs: 0
_xcode: -532459699
Message: "XML parsing error: Invalid at the top level of the document."
InnerException: null
TargetSite: {System.Reflection.RuntimeMethodInfo}
StackTrace: " at
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior,
RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteScalar()
at BenefitCentral.Entities.Company.Save(SqlConnection conn) in
c:\bcdotnet\entities\company.cs:line 110
at BenefitCentral.Tester.lstQuotes_SelectedIndexChanged(Object sender,
EventArgs e) in c:\bcdotnet\tester.aspx.cs:line 442"
HelpLink: ""
Source: ".Net SqlClient Data Provider"
HResult: -2146232060
The stored procedure I'm calling:
CREATE procedure sp_SaveCompany(@sxml text) as
EXEC sp_xml_preparedocument @idoc OUTPUT, @sxml
(snip)
And, finally, the XML document I'm passing (sorry, it's kinda big). Had to
edit out some personal info, but that's it.
<?xml version="1.0"?>
<EmpGroup xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<EffectiveDate>2002-06-01T00:00:00.0000000-04:00</EffectiveDate>
<iOwnerId>12</iOwnerId>
<address>
<AddressOne />
<AddressTwo />
<City>Springfield</City>
<State>MA</State>
<ZipCode>01102</ZipCode>
<ContactInfo>
<Type>Telephone_Number</Type>
<Value />
</ContactInfo>
</address>
<Contactinfo>
<Type>Telephone_Number</Type>
<Value />
</Contactinfo>
<Name>Amy</Name>
<ID>23110</ID>
<SICCode />
<SalaryClassList />
<OwnerId>12</OwnerId>
<Employees>
<anyType xsi:type="Person">
<Age>53</Age>
<SalaryClasses />
<DependantLife>false</DependantLife>
<Retired>false</Retired>
<Zipcode>02420</Zipcode>
<HireDate>0001-01-01T00:00:00.0000000-05:00</HireDate>
<Comments />
<Salary />
<Occupation />
<DentalCoverage>0</DentalCoverage>
<VisionCoverage>0</VisionCoverage>
<Gender>Female</Gender>
<DOB>1948-10-06T00:00:00.0000000-04:00</DOB>
<IsCobra>false</IsCobra>
<CoverageLevel>WAV</CoverageLevel>
<ID>391005</ID>
<Firstname>Jonathan</Firstname>
<OwnerId>2</OwnerId>
</anyType>
<anyType xsi:type="Person">
<Age>48</Age>
<SalaryClasses />
<DependantLife>false</DependantLife>
<Retired>false</Retired>
<Zipcode>02420</Zipcode>
<HireDate>0001-01-01T00:00:00.0000000-05:00</HireDate>
<Comments />
<Salary />
<Occupation />
<DentalCoverage>0</DentalCoverage>
<VisionCoverage>0</VisionCoverage>
<Gender>Male</Gender>
<DOB>1953-12-19T00:00:00.0000000-05:00</DOB>
<IsCobra>false</IsCobra>
<CoverageLevel>FAM</CoverageLevel>
<ID>391006</ID>
<Firstname />
<OwnerId>2</OwnerId>
</anyType>
<anyType xsi:type="Person">
<Age>56</Age>
<SalaryClasses />
<DependantLife>false</DependantLife>
<Retired>false</Retired>
<Zipcode>02155</Zipcode>
<HireDate>0001-01-01T00:00:00.0000000-05:00</HireDate>
<Comments />
<Salary />
<Occupation />
<DentalCoverage>0</DentalCoverage>
<VisionCoverage>0</VisionCoverage>
<Gender>Male</Gender>
<DOB>1946-01-27T00:00:00.0000000-05:00</DOB>
<IsCobra>false</IsCobra>
<CoverageLevel>FAM</CoverageLevel>
<ID>391007</ID>
<Firstname />
<OwnerId>2</OwnerId>
</anyType>
<anyType xsi:type="Person">
<Age>38</Age>
<SalaryClasses />
<DependantLife>false</DependantLife>
<Retired>false</Retired>
<Zipcode>01742</Zipcode>
<HireDate>0001-01-01T00:00:00.0000000-05:00</HireDate>
<Comments />
<Salary />
<Occupation />
<DentalCoverage>0</DentalCoverage>
<VisionCoverage>0</VisionCoverage>
<Gender>Male</Gender>
<DOB>1963-11-14T00:00:00.0000000-05:00</DOB>
<IsCobra>false</IsCobra>
<CoverageLevel>FAM</CoverageLevel>
<ID>391008</ID>
<Firstname />
<OwnerId>2</OwnerId>
</anyType>
<anyType xsi:type="Person">
<Age>39</Age>
<SalaryClasses />
<DependantLife>false</DependantLife>
<Retired>false</Retired>
<Zipcode>02482</Zipcode>
<HireDate>0001-01-01T00:00:00.0000000-05:00</HireDate>
<Comments />
<Salary />
<Occupation />
<DentalCoverage>0</DentalCoverage>
<VisionCoverage>0</VisionCoverage>
<Gender>Male</Gender>
<DOB>1963-01-12T00:00:00.0000000-05:00</DOB>
<IsCobra>false</IsCobra>
<CoverageLevel>FAM</CoverageLevel>
<ID>391009</ID>
<Firstname />
<OwnerId>2</OwnerId>
</anyType>
<anyType xsi:type="Person">
<Age>22</Age>
<SalaryClasses />
<DependantLife>false</DependantLife>
<Retired>false</Retired>
<Zipcode>02467</Zipcode>
<HireDate>0001-01-01T00:00:00.0000000-05:00</HireDate>
<Comments />
<Salary />
<Occupation />
<DentalCoverage>0</DentalCoverage>
<VisionCoverage>0</VisionCoverage>
<Gender>Female</Gender>
<DOB>1979-11-03T00:00:00.0000000-05:00</DOB>
<IsCobra>false</IsCobra>
<CoverageLevel>WAV</CoverageLevel>
<ID>391010</ID>
<Firstname />
<OwnerId>2</OwnerId>
</anyType>
<anyType xsi:type="Person">
<Age>33</Age>
<SalaryClasses />
<DependantLife>false</DependantLife>
<Retired>false</Retired>
<Zipcode>01945</Zipcode>
<HireDate>0001-01-01T00:00:00.0000000-05:00</HireDate>
<Comments />
<Salary />
<Occupation />
<DentalCoverage>0</DentalCoverage>
<VisionCoverage>0</VisionCoverage>
<Gender>Male</Gender>
<DOB>1969-01-03T00:00:00.0000000-05:00</DOB>
<IsCobra>false</IsCobra>
<CoverageLevel>FAM</CoverageLevel>
<ID>391011</ID>
<Firstname/>
<OwnerId>2</OwnerId>
</anyType>
<anyType xsi:type="Person">
<Age>34</Age>
<SalaryClasses />
<DependantLife>false</DependantLife>
<Retired>false</Retired>
<Zipcode>02144</Zipcode>
<HireDate>0001-01-01T00:00:00.0000000-05:00</HireDate>
<Comments />
<Salary />
<Occupation />
<DentalCoverage>0</DentalCoverage>
<VisionCoverage>0</VisionCoverage>
<Gender>Male</Gender>
<DOB>1968-05-10T00:00:00.0000000-04:00</DOB>
<IsCobra>false</IsCobra>
<CoverageLevel>EE</CoverageLevel>
<ID>391012</ID>
<Firstname />
<OwnerId>2</OwnerId>
</anyType>
<anyType xsi:type="Person">
<Age>28</Age>
<SalaryClasses />
<DependantLife>false</DependantLife>
<Retired>false</Retired>
<Zipcode>01887</Zipcode>
<HireDate>0001-01-01T00:00:00.0000000-05:00</HireDate>
<Comments />
<Salary />
<Occupation />
<DentalCoverage>0</DentalCoverage>
<VisionCoverage>0</VisionCoverage>
<Gender>Female</Gender>
<DOB>1973-11-18T00:00:00.0000000-05:00</DOB>
<IsCobra>false</IsCobra>
<CoverageLevel>EE</CoverageLevel>
<ID>391013</ID>
<Firstname />
<OwnerId>2</OwnerId>
</anyType>
<anyType xsi:type="Person">
<Age>52</Age>
<SalaryClasses />
<DependantLife>false</DependantLife>
<Retired>false</Retired>
<Zipcode>01561</Zipcode>
<HireDate>0001-01-01T00:00:00.0000000-05:00</HireDate>
<Comments />
<Salary />
<Occupation />
<DentalCoverage>0</DentalCoverage>
<VisionCoverage>0</VisionCoverage>
<Gender>Female</Gender>
<DOB>1950-04-12T00:00:00.0000000-04:00</DOB>
<IsCobra>false</IsCobra>
<CoverageLevel>EE</CoverageLevel>
<ID>391014</ID>
<Firstname />
<OwnerId>2</OwnerId>
</anyType>
<anyType xsi:type="Person">
<Age>34</Age>
<SalaryClasses />
<DependantLife>false</DependantLife>
<Retired>false</Retired>
<Zipcode>02474</Zipcode>
<HireDate>0001-01-01T00:00:00.0000000-05:00</HireDate>
<Comments />
<Salary />
<Occupation />
<DentalCoverage>0</DentalCoverage>
<VisionCoverage>0</VisionCoverage>
<Gender>Male</Gender>
<DOB>1967-09-19T00:00:00.0000000-04:00</DOB>
<IsCobra>false</IsCobra>
<CoverageLevel>EE</CoverageLevel>
<ID>391015</ID>
<Firstname />
<OwnerId>2</OwnerId>
</anyType>
<anyType xsi:type="Person">
<Age>43</Age>
<SalaryClasses />
<DependantLife>false</DependantLife>
<Retired>false</Retired>
<Zipcode>02478</Zipcode>
<HireDate>0001-01-01T00:00:00.0000000-05:00</HireDate>
<Comments />
<Salary />
<Occupation />
<DentalCoverage>0</DentalCoverage>
<VisionCoverage>0</VisionCoverage>
<Gender>Male</Gender>
<DOB>1958-11-22T00:00:00.0000000-05:00</DOB>
<IsCobra>false</IsCobra>
<CoverageLevel>FAM</CoverageLevel>
<ID>391016</ID>
<Firstname />
<OwnerId>2</OwnerId>
</anyType>
<anyType xsi:type="Person">
<Age>47</Age>
<SalaryClasses />
<DependantLife>false</DependantLife>
<Retired>false</Retired>
<Zipcode>02493</Zipcode>
<HireDate>0001-01-01T00:00:00.0000000-05:00</HireDate>
<Comments />
<Salary />
<Occupation />
<DentalCoverage>0</DentalCoverage>
<VisionCoverage>0</VisionCoverage>
<Gender>Male</Gender>
<DOB>1955-02-07T00:00:00.0000000-05:00</DOB>
<IsCobra>false</IsCobra>
<CoverageLevel>FAM</CoverageLevel>
<ID>391017</ID>
<Firstname />
<OwnerId>2</OwnerId>
</anyType>
<anyType xsi:type="Person">
<Age>43</Age>
<SalaryClasses />
<DependantLife>false</DependantLife>
<Retired>false</Retired>
<Zipcode>02421</Zipcode>
<HireDate>0001-01-01T00:00:00.0000000-05:00</HireDate>
<Comments />
...
read more »