CODEWITHSUNDEEP
xmlxsd
raj K
raj K

Reputation: 13

SQL bulk load not loading XML with additional namespace

I'm using SQLXMLBULKLOADLib and below works without additional namespace xmlns="http://schemas.datacontract.org/2004/07/test.test". How to make XSD and bulk load utility with additional namespace?

This XML works fine without second name space xmlns:

<ROOT xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/test.test">  
  <Customers>  
    <CustomerID>1111</CustomerID>  
    <CompanyName>Sean Chai</CompanyName>  
    <City>NY</City>  
  </Customers>  
  <Customers>  
    <CustomerID>1112</CustomerID>  
    <CompanyName>Tom Johnston</CompanyName>  
     <City>LA</City>     
  </Customers> 
</ROOT>

Schema File:

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
            xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
      <xsd:element name="ROOT" sql:is-constant="1" >
         <xsd:complexType>
          <xsd:sequence>
        <xsd:element name="Customers" sql:relation="Cust" >
          <xsd:complexType>
            <xsd:sequence>
              <xsd:element name="CustomerID"  type="xsd:integer" />
              <xsd:element name="CompanyName" type="xsd:string" />
              <xsd:element name="City"        type="xsd:string" />
           </xsd:sequence>
          </xsd:complexType>
        </xsd:element>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

How to add reference to additional namespace in XSD so that xmlbulkload utility will insert data?

Upvotes: 0

Views: 122

Answers (1)

Sprotty
Sprotty

Reputation: 6003

The XML data does not validate against the schema.

The xml document places all the elements into the namespace 'http://schemas.datacontract.org/2004/07/test.test', but the schema has not expecting them to be in a namespace as the targetnamespace so not set.

Also your cardinality on 'Customer' is wrong (1-1), and you have not set the schema attribute elementFormDefault="qualified", without this set, every element in the XML document must be qualified with a namespace.

So to make your XML data valid against the schema your schema should look like this.

enter image description here 

<?xml version="1.0" encoding="utf-8" ?>
<!-- Created with Liquid XML 2017 Developer Bundle Edition (Trial) 15.0.0.6978 (https://www.liquid-technologies.com) -->
<xsd:schema xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
            xmlns:i="http://www.w3.org/2001/XMLSchema-instance"
            elementFormDefault="qualified"
            targetNamespace="http://schemas.datacontract.org/2004/07/test.test"
            xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <xsd:element name="ROOT"
                 sql:is-constant="1">
        <xsd:complexType>
            <xsd:sequence>
                <xsd:element name="Customers"
                             minOccurs="0"
                             maxOccurs="unbounded"
                             sql:relation="Cust">
                    <xsd:complexType>
                        <xsd:sequence>
                            <xsd:element name="CustomerID"
                                         type="xsd:integer"/>
                            <xsd:element name="CompanyName"
                                         type="xsd:string"/>
                            <xsd:element name="City"
                                         type="xsd:string"/>
                        </xsd:sequence>
                    </xsd:complexType>
                </xsd:element>
            </xsd:sequence>
        </xsd:complexType>
    </xsd:element>
</xsd:schema>

Upvotes: 2

Related Questions