在 MSSQL 2005 中使用 OPENXML 获取 dt 元素。
如何在 xml 中获取 xmlns:dt 元素？例如，获取包含产品 ID 和国家/地区代码的两行结果集。

121403 英镑

121403 美国

declare @xmldata xml 
    set @xmldata =  
    '<?xml version="1.0"?> 
    <data xmlns="http://www.aaa.com/master_browse_response" xmlns:dt="http://www.aaa.com/DataTypes"> 
      <products> 
        <product> 
       <product_id><![CDATA[121403]]></product_id> 
          <countries> 
            <dt:country>GBR</dt:country> 
            <dt:country>USA</dt:country> 
          </countries> 
        </product> 
     </products> 
    </data>' 
 
     DECLARE @hDoc int, @rootxmlns varchar(100) 
     SET @rootxmlns = '<root xmlns:hm="http://www.aaa.com/master_browse_response"/>' 
 
     EXEC sp_xml_preparedocument @hDoc OUTPUT, @xmldata, @rootxmlns   
 
     SELECT * 
     FROM OPENXML(@hDoc, '//hm:product',2) 
     WITH ([hm:product_id] int , [hm:countries] varchar(100)) 
 
     --clean up  
     EXEC sp_xml_removedocument @hDoc 

DECLARE @hDoc int, @rootxmlns varchar(100) 
SET @rootxmlns = '<root xmlns:hm="http://www.aaa.com/master_browse_response"/>' 
 
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xmldata, @rootxmlns   
 
CREATE TABLE #xmlEdgeTable 
(  
    id int,  
    parentid int, 
    localname varchar(20),  
    [text] varchar(20) 
) 
 
INSERT INTO #xmlEdgeTable 
SELECT id, parentid,localname, cast([text] as varchar(20))  
FROM OPENXML(@hDoc, '//hm:product',2) 
 
SELECT t6.text, t2.text FROM #xmlEdgeTable AS t1 INNER JOIN  
    #xmlEdgeTable AS t2 ON t1.id = t2.parentid INNER JOIN  
    #xmlEdgeTable AS t3 ON t3.id = t1.parentid INNER JOIN  
    #xmlEdgeTable AS t4 ON t4.id = t3.parentid INNER JOIN  
    #xmlEdgeTable AS t5 ON t4.id = t5.parentid INNER JOIN 
    #xmlEdgeTable AS t6 ON t5.id = t6.parentid  
WHERE t1.localname = 'country' and t5.localname ='product_id' 
 
--clean up  
EXEC sp_xml_removedocument @hDoc 
DROP TABLE #xmlEdgeTable 

请您参考如下方法:

是否有特殊原因需要使用 OPENXML 来执行此操作？您可以在 2005 年使用 XQUERY 轻松获取信息，如下所示:

declare @xmldata xml     
set @xmldata =  
'<data xmlns="http://www.aaa.com/master_browse_response" xmlns:dt="http://www.aaa.com/DataTypes"> 
  <products> 
    <product> 
      <product_id>121403</product_id> 
      <countries> 
        <dt:country>GBR</dt:country> 
        <dt:country>USA</dt:country> 
      </countries> 
    </product> 
  </products> 
</data>' 
 
;WITH XMLNAMESPACES  
( 
    DEFAULT 'http://www.aaa.com/master_browse_response', 
    'http://www.aaa.com/DataTypes' as dt 
) 
SELECT  x.c.value('(../../product_id)[1]', 'varchar(100)') as product_id, 
        x.c.value('(.)[1]', 'varchar(100)') as country 
FROM @xmldata.nodes('/data/products/product/countries/dt:country') x(c) 

declare @xmldata xml     
set @xmldata =  
'<data xmlns="http://www.aaa.com/master_browse_response" xmlns:dt="http://www.aaa.com/DataTypes"> 
  <products> 
    <product> 
      <product_id>121403</product_id> 
      <countries> 
        <dt:country>GBR</dt:country> 
        <dt:country>USA</dt:country> 
      </countries> 
    </product> 
  </products> 
</data>' 
 
DECLARE @hDoc int, @rootxmlns varchar(100) 
SET @rootxmlns = '<root xmlns:hm="http://www.aaa.com/master_browse_response" xmlns:dt="http://www.aaa.com/DataTypes"/>' 
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xmldata, @rootxmlns 
 
SELECT * 
FROM OPENXML(@hDoc, '//hm:product/hm:countries/dt:country',2) 
        WITH(Country    varchar(100) '.', 
             Product_ID varchar(100) '../../hm:product_id') 
 
EXEC sp_xml_removedocument @hDoc