我有一个存储XML
数据的列。在此数据中,我需要在另一个field = xxxx
时报告一个字段。
这是我的XML:
<?xml version="1.0" encoding="utf-16"?>
<SourceIdentifierPairs xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas">
<SourceIdentifierPair>
<Source>ABCPN</Source>
<Identifier>123456789</Identifier>
</SourceIdentifierPair>
<SourceIdentifierPair>
<Source>ABCMR</Source>
<Identifier>000000123654</Identifier>
</SourceIdentifierPair>
<SourceIdentifierPair>
<Source>PRIM</Source>
<Identifier>00112233</Identifier>
</SourceIdentifierPair>
</SourceIdentifierPairs>
当电源为ABCPN
时,需要拉Identifier
。
您要查找的XPath表达式是
//SourceIdentifierPair[Source='ABCPN']/Identifier
确保注册默认命名空间。
示例代码:
declare @x xml = '<SourceIdentifierPairs xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas">
<SourceIdentifierPair>
<Source>ABCPN</Source>
<Identifier>123456789</Identifier>
</SourceIdentifierPair>
<SourceIdentifierPair>
<Source>ABCMR</Source>
<Identifier>000000123654</Identifier>
</SourceIdentifierPair>
<SourceIdentifierPair>
<Source>PRIM</Source>
<Identifier>00112233</Identifier>
</SourceIdentifierPair>
</SourceIdentifierPairs>'
--xpath as above, with default namespace specified
;with xmlnamespaces ('http://schemas' as ns1, default 'http://schemas')
select @x.value('(//SourceIdentifierPair[Source=''ABCPN'']/Identifier)[1]', 'nvarchar(32)') IdentifierABCPN
--more complex version of the above code (may be useful should you have more complex requirements in future)
;with xmlnamespaces ('http://schemas' as ns1)
select @x.value('(/ns1:SourceIdentifierPairs/ns1:SourceIdentifierPair[./ns1:Source/text()[.=''ABCPN'']]/ns1:Identifier/text())[1]', 'nvarchar(32)') IdentifierABCPN