MS SQLXML. Import XML with specified namespace

Question

I have to import XML data into a SQL Server database. I have the following XML (for example):

<ROOT xmlns="http://www.w3.org/1999/xhtml">  
  <Customer CustomerID="1" CompanyName="xyz" />
  <Customer CustomerID="2" CompanyName="abc" />
</ROOT>

With the schema:

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
  <xsd:element name="Customer" sql:relation="Customers" >
   <xsd:complexType>
     <xsd:attribute name="CustomerID" type="xsd:string" />
     <xsd:attribute name="CompanyName" type="xsd:string" />
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

And running standard MSDN (http://msdn.microsoft.com/ru-ru/library/ms172633.aspx) VBScript SQLXMLBulkLoad will succeed, however no data will be loaded.

If you delete the namespace reference (xmlns attribute), it will work.

XSD doesn't allow you to map attribute 'xmlns', so sql:mapped='false' does not work here.

Problem is, source XML cannot be changed, is it even possible to solve the issue using only XSD schema?

Answer 1

Seems pretty simple with standard XQuery that's available in SQL Server 2005 or newer:

DECLARE @XmlData XML = '<ROOT xmlns="http://www.w3.org/1999/xhtml">  
  <Customer CustomerID="1" CompanyName="xyz" />
  <Customer CustomerID="2" CompanyName="abc" />
</ROOT>'

;WITH XMLNAMESPACES(DEFAULT 'http://www.w3.org/1999/xhtml')
SELECT
    ID = XC.value('@CustomerID', 'int'),
    CompanyName = XC.value('@CompanyName', 'varchar(50)')
FROM 
    @XmlData.nodes('/ROOT/Customer') XT(XC)

Gives me an output of:

which could very easily be used in a INSERT INTO .... statement, too