带有 xmlns:dt 的 OPENXML
在 MSSQL 2005 中使用 OPENXML 获取 dt 元素.如何在 xml 中获取 xmlns:dt 元素?例如,获取包含产品 ID 和国家/地区代码的两行结果集.
Use OPENXML to get dt element in MSSQL 2005. How can I get xmlns:dt element in xml? For example, get a result set of two rows that list product id and country code.
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
这是我使用 xmlEdgeTable 知道的一个解决方案,但我正在寻找更好的解决方案.
Here is one solution that I know by using xmlEdgeTable, but I am looking for a better solution.
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 轻松获取信息,如下所示:
Is there a particular reason that you need to use OPENXML to do this? You can easily get the information with a XQUERY in 2005 like this:
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)
较新的 XQUERY 功能是解决问题的更好选择.
The newer XQUERY capabilities are a much better choice for solving your problem.
与 OPENXML 相同的解决方案是:
The same solution with OPENXML would be:
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
相关文章