title: Getting NULL values while using OPENXML to read from XML file
body: <p>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.</p>
  
  <p>this XML uses DTD.</p>
  
  <p>Thanks for help.</p>
  
  <pre><code>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
  
  
  &lt;!-- Generated by crossbase mediasolution GmbH (http://www.crossbase.de) --&gt;
  &lt;BMECAT xmlns="http://www.bmecat.org/bmecat/1.2/bmecat_new_catalog" version="1.2"&gt;
  &lt;HEADER&gt;
  &lt;CATALOG&gt;
    &lt;LANGUAGE&gt;DE&lt;/LANGUAGE&gt;
    &lt;CATALOG_ID&gt;ML103&lt;/CATALOG_ID&gt;
    &lt;CATALOG_VERSION&gt;1&lt;/CATALOG_VERSION&gt;
    &lt;DATETIME type="generation_date"&gt;
      &lt;DATE&gt;2015-10-12&lt;/DATE&gt;
      &lt;TIME&gt;07:10:42&lt;/TIME&gt;
      &lt;TIMEZONE&gt;+02:00&lt;/TIMEZONE&gt;
    &lt;/DATETIME&gt;
  &lt;/CATALOG&gt;
  &lt;BUYER&gt;
    &lt;ADDRESS type="buyer" /&gt;
  &lt;/BUYER&gt;
  &lt;SUPPLIER&gt;
    &lt;SUPPLIER_NAME&gt;GmbH&lt;/SUPPLIER_NAME&gt;
    &lt;ADDRESS type="supplier"&gt;
      &lt;NAME&gt;GmbH&lt;/NAME&gt;
      &lt;NAME2&gt;fabrik&lt;/NAME2&gt;
      &lt;STREET&gt;e 1&lt;/STREET&gt;
      &lt;ZIP&gt;6973&lt;/ZIP&gt;
      &lt;CITY&gt;st&lt;/CITY&gt;
      &lt;COUNTRY&gt;eich&lt;/COUNTRY&gt;
      &lt;PHONE&gt;05-0&lt;/PHONE&gt;
      &lt;FAX&gt;705-44&lt;/FAX&gt;
      &lt;EMAIL&gt;info@com&lt;/EMAIL&gt;
      &lt;URL&gt;www.com&lt;/URL&gt;
    &lt;/ADDRESS&gt;
  &lt;/SUPPLIER&gt;
  </code></pre>
  
  <p>
    </p>