从XML列读取属性值

Question

如何从XML数据中提取属性的值？

drop table #demo2
create table #demo2 (field1 xml)
insert into #demo2 (field1) 
values (
'<root xmlns="system/schema" vesion="1.0">
    <header msgtype="1">
        <msgid>1</msgid>
    </header>
    <transaction>
        <systemevent>
            <eventdetail eventtype="100">
                <eventstamp unitid="87888" value="2021-11-14T01:44:41.069Z" />
                <eventitem id="1" value="abc123" />
            </eventdetail>
            <eventdetail eventtype="102">
                <eventstamp unitid="87889" value="2021-11-14T01:44:41.704Z" />
                <eventitem id="2" />
            </eventdetail>
        </systemevent>
    </transaction>
</root>')

我期望这个XML的答案是abc123 & null。下面是我试图获得期望值的查询。

select  x.v.value('@value','VARCHAR(100)') AS EventValue,
        y.v.value('@value','VARCHAR(100)') AS EventValue2
from #demo2 t
cross apply field1.nodes('//eventdetail[@eventtype="100"]/eventitem') x(v)
cross apply field1.nodes('//eventdetail[@eventtype="102"]/eventitem') y(v)

我不确定我的xpath中缺少了什么来获取该值。谢谢你的帮助。

Answer 1

这一点：

<root xmlns="system/schema" vesion="1.0">

意味着文档有一个default namespace，所以所有元素实际上都在名称空间中。那个文档等同于这个文档：

<foo:root xmlns:foo="system/schema" vesion="1.0">
  <foo:header msgtype="1">
    <foo:msgid>1</foo:msgid>
  </foo:header>
  <foo:transaction>
    <foo:systemevent>
      <foo:eventdetail eventtype="100">
        <foo:eventstamp unitid="87888" value="2021-11-14T01:44:41.069Z" />
        <foo:eventitem id="1" value="abc123" />
      </foo:eventdetail>
      <foo:eventdetail eventtype="102">
        <foo:eventstamp unitid="87889" value="2021-11-14T01:44:41.704Z" />
        <foo:eventitem id="2" />
      </foo:eventdetail>
    </foo:systemevent>
  </foo:transaction>
</foo:root>

因此您必须使用WITH XMLNAMESPACES对查询进行命名空间限定，例如

WITH XMLNAMESPACES (DEFAULT 'system/schema')  
select  x.v.value('@value','VARCHAR(100)') AS EventValue,
        y.v.value('@value','VARCHAR(100)') AS EventValue2
from #demo2 t
cross apply field1.nodes('//eventdetail[@eventtype="100"]/eventitem') x(v)
cross apply field1.nodes('//eventdetail[@eventtype="102"]/eventitem') y(v)

在大多数情况下，您还应该完全限定元素XPath，而不是使用//。例如

WITH XMLNAMESPACES (DEFAULT 'system/schema')  
select  x.v.value('@value','VARCHAR(100)') AS EventValue,
        y.v.value('@value','VARCHAR(100)') AS EventValue2
from #demo2 t
cross apply field1.nodes('/root/transaction/systemevent/eventdetail[@eventtype="100"]/eventitem') x(v)
cross apply field1.nodes('/root/transaction/systemevent/eventdetail[@eventtype="102"]/eventitem') y(v)