我尝试读取 sql server 中的 xml 文件:
DECLARE @XMLToParse XML
-- Load the XML data in to a variable to work with.
-- This would typically be passed as a parameter to a stored proc
SET @XMLToParse = '<Response xmlns="http://tempuri.org/">
<AuthResult xmlns:a="http://schemas.datacontract.org/2004/07/Sistema.Soap.Contracts" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
<a:Result>
<a:Id_Value>500</a:Id_Value>
</a:Result>
<a:Result>
<a:Id_Value>895</a:Id_Value>
</a:Result>
</AuthResult>
</Response>'
-- Declare temp table to parse data into
DECLARE @ParsingTable TABLE
(Id_Value INT)
-- Parse the XML in to the temp table declared above
INSERT
INTO @ParsingTable
(Id_Value)
SELECT xmlData.A.value('.', 'INT') AS Id_Value
FROM @XMLToParse.nodes('Response/AuthResult/Result') xmlData(A)
-- Insert into the actual table from the temp table
SELECT Id_Value
FROM @ParsingTable
显然代码是正确的,但我无法获取值,我哪里可能错了?
Use WITH XMLNAMESPACES声明命名空间并对不在默认命名空间中的节点使用相应的前缀。
...
-- Parse the XML in to the temp table declared above
;WITH XMLNAMESPACES (DEFAULT 'http://tempuri.org/',
'http://schemas.datacontract.org/2004/07/Sistema.Soap.Contracts' as a,
'http://www.w3.org/2001/XMLSchema-instance' as i
)
INSERT
INTO @ParsingTable
(Id_Value)
SELECT xmlData.A.value('.', 'INT') AS Id_Value
FROM @XMLToParse.nodes('Response/AuthResult/a:Result') xmlData(A)
...
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)