CODEWITHSUNDEEP
c#xmlxsdsqlxmlbulk-load
Richard Hansell
Richard Hansell

Reputation: 5403

SQLXMLBULKLOAD Utility doesn't work with the namespace in my XML

So I have XML I can't change, and it basically looks something like this:

<?xml version="1.0" encoding="UTF-8"?>
<ns6:Responses xmlns:ns6="http://www.yadayada" xmlns:ns2="http://www.yadayada" xmlns:ns3="http://www.w3.org/2000/09/xmldsig#" xmlns:ns4="http://www.yadayada" xmlns:ns5="http://www.yadayada">
  <MessageReference>824cf96b-6130-460c-a2d4-2adc3b6ea14d</MessageReference>
</ns6:Responses>

I use a hand-crafted XSD that has the SQL annotations, and the part that is relevant is this:

  <xs:element name="Responses" msdata:Prefix="ns6" sql:relation="Responses">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="MessageReference" type="xs:string" msdata:Prefix="ns6" sql:field="MessageReference" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>

When I push this through the SQLXMLBULKLOAD Utility it doesn't throw an error, but nothing ends up in my database.

I can get this to work if I do one of the following:

My guess is that the processing is expecting the elements within ns6:Responses to also be prefixed with ns6, but they aren't in my XML, and I can't change this.

I have tried the following, which didn't work:

The problem is that I can change the XSD, but I can't change the XML. So my two working solutions are no good to me, as they both involve changes to the XML.

I know I could pre-process the XML, either removing the ns6: namespace or adding it to all elements, but I wanted to try and avoid this.

Upvotes: 1

Views: 122

Answers (1)

Richard Hansell
Richard Hansell

Reputation: 5403

Well a rather disappointing response, but I worked this out myself.

There are two ways to fix this, either change elementFormDefault="qualified" to elementFormDefault="unqualified", or add form="unqualified" to every element that isn't in the ns6 namespace.

So method #1 is to change the msdata namespace to: xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" attributeFormDefault="qualified" elementFormDefault="unqualified".

Method #2 is to change the body of the XSD to this:

  <xs:element name="Responses" msdata:Prefix="ns6" sql:relation="Responses">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="MessageReference" form="unqualified" type="xs:string" msdata:Prefix="ns6" sql:field="MessageReference" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>

Upvotes: 1

Related Questions