sql - 使用 OPENXML 从 XML 文件读取时获取 NULL 值

Question

为什么我在尝试将 XML 文档(下面的文件示例)读入 SQL Server 时得到 NULL 值？我认为这是由于阅读文件。 SQL 的第一部分似乎没问题——我在 SQLSERVER 网格中看到了 xml 文件。但不知道如何读取数据。想法是 - 我想将 XML 文件导入到 SQL SERVER 中，使用简单的 SQL 查询进行一些数据清理，然后将其导入到其他系统中。

此 XML 使用 DTD。

感谢您的帮助。

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

感谢原始文件。我把它下载下来，可以毫无问题地阅读它:。像这样尝试:

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

接下来您会找到一堆 XML 示例(有或没有命名空间)来比较并找到您需要的最佳方法。只需将其粘贴到一个空的查询窗口中并执行:

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'  )