Getting NULL values while using OPENXML to read from XML file

Question

Why I get NULL values while trying to read the XML document (file example below) into SQL server? I think its due to reading the file. First part of SQL seems to be ok - I see the xml file in SQLSERVER grid. But dont know, how to read the data. Idea is - that I want to import the XML file into SQL SERVER, do some data cleansing with simple SQL queries, then import that into other system.

this XML uses DTD.

Thanks for help.

SELECT CONVERT(XML, BulkColumn,2) AS BulkColumn
FROM OPENROWSET(BULK 'H:\file.xml', SINGLE_BLOB) AS x;

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)    

SELECT @XML = XMLData FROM XMLwithOpenXML WHERE ID = '1' -- The row to process    

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML


SELECT *
FROM OPENXML(@hDoc, '/BMECAT/HEADER/CATALOG/',1)
WITH 
(
language nvarchar(max)
)


EXEC sp_xml_removedocument @hDoc
GO


<!-- Generated by crossbase mediasolution GmbH (http://www.crossbase.de) -->
<BMECAT xmlns="http://www.bmecat.org/bmecat/1.2/bmecat_new_catalog" version="1.2">
<HEADER>
<CATALOG>
  <LANGUAGE>DE</LANGUAGE>
  <CATALOG_ID>ML103</CATALOG_ID>
  <CATALOG_VERSION>1</CATALOG_VERSION>
  <DATETIME type="generation_date">
    <DATE>2015-10-12</DATE>
    <TIME>07:10:42</TIME>
    <TIMEZONE>+02:00</TIMEZONE>
  </DATETIME>
</CATALOG>
<BUYER>
  <ADDRESS type="buyer" />
</BUYER>
<SUPPLIER>
  <SUPPLIER_NAME>GmbH</SUPPLIER_NAME>
  <ADDRESS type="supplier">
    <NAME>GmbH</NAME>
    <NAME2>fabrik</NAME2>
    <STREET>e 1</STREET>
    <ZIP>6973</ZIP>
    <CITY>st</CITY>
    <COUNTRY>eich</COUNTRY>
    <PHONE>05-0</PHONE>
    <FAX>705-44</FAX>
    <EMAIL>info@com</EMAIL>
    <URL>www.com</URL>
  </ADDRESS>
</SUPPLIER>

Answer 1

Thx for the original file. I loaded it down and could read this without problems:. Try it like this:

DECLARE @yourXML AS XML=
(
SELECT CONVERT(XML, BulkColumn,2) AS BulkColumn
FROM OPENROWSET(BULK 'H:\file.xml', SINGLE_BLOB) AS x
);

--simple approach
WITH XMLNAMESPACES(DEFAULT 'http://www.bmecat.org/bmecat/1.2/bmecat_new_catalog')
SELECT @YourXML.value('(/BMECAT/HEADER/CATALOG/LANGUAGE)[1]','varchar(2)');

--with OPENXML
DECLARE @hDoc INT;
EXEC sp_xml_preparedocument @hDoc output, @YourXML,'<root xmlns:dflt="http://www.bmecat.org/bmecat/1.2/bmecat_new_catalog" />';

SELECT LANGUAGE
FROM OPENXML(@hDoc,'/dflt:BMECAT/dflt:HEADER/dflt:CATALOG') 
WITH(LANGUAGE VARCHAR(MAX) 'dflt:LANGUAGE'); 

EXEC sp_xml_removedocument @hDoc
GO

Following you find a bunch of XML examples - with or without namespaces - to compare and find the best approach you need. Just paste this into an empty query window and execute:

DECLARE @xmlNaked XML='<root><element test="SomeValue"/></root>';
DECLARE @xmlNamespace XML='<root xmlns="http://testNS" xmlns:ns2="http://testNS/ns2"><element test="SomeValue"/><ns2:namespaced test2="another value"/></root>';

--Normale approach
SELECT @xmlNaked.value('(/root/element/@test)[1]','varchar(max)') AS TheElement;

--No return value due to namespace
SELECT @xmlNamespace.value('(/root/element/@test)[1]','varchar(max)') AS TheElementMissing
      ,@xmlNamespace.value('(/root/namespaced/@test2)[1]','varchar(max)') AS NamespacedMissing; 

--Declaring default namespace before (xmlns is the default, ns2 additional)
WITH XMLNAMESPACES(DEFAULT 'http://testNS')
SELECT @xmlNamespace.value('(/root/element/@test)[1]','varchar(max)') AS TheElement
      ,@xmlNamespace.value('(/root/namespaced/@test2)[1]','varchar(max)') AS NamespacedMissing; 

--Declaring default namespace and additional namespace
WITH XMLNAMESPACES('http://testNS/ns2' AS ns2, DEFAULT 'http://testNS')
SELECT @xmlNamespace.value('(/root/element/@test)[1]','varchar(max)') AS TheElement
      ,@xmlNamespace.value('(/root/ns2:namespaced/@test2)[1]','varchar(max)') AS NamespacedMissing; 

--now with OPENXML, namespaces must be introduced in sp_xml_preparedocument
DECLARE @i INT, @ns VARCHAR(100);
EXEC sp_xml_preparedocument @i output, @xmlNaked;
SELECT test AS TheElement FROM OPENXML(@i,'/root/element',2) WITH(test VARCHAR(MAX) '@test') 

--not return value due to namespace
EXEC sp_xml_preparedocument @i output, @xmlNamespace;
SELECT test AS TheElementMissing FROM OPENXML(@i,'/root/element',2) WITH(test VARCHAR(MAX) '@test') 

--There's AFAIK no "direct" declaration of the default namespace possible. Look at the declaration of "dflt" namespace
EXEC sp_xml_preparedocument @i output, @xmlNamespace,'<root xmlns:dflt="http://testNS" />';
SELECT test AS TheElement FROM OPENXML(@i,'/dflt:root/dflt:element') WITH(test VARCHAR(MAX)) 

--Now the full thing
EXEC sp_xml_preparedocument @i output, @xmlNamespace,'<root xmlns:dflt="http://testNS" xmlns:ns2="http://testNS/ns2"/>';
SELECT test AS TheElement,test2 AS Namespace 
FROM OPENXML(@i,'/dflt:root',2) 
     WITH   (test VARCHAR(MAX) 'dflt:element/@test'
            ,test2 VARCHAR(MAX) 'ns2:namespaced/@test2'  )