Reading attribute value from XML column-CodePudding

How do I extract the value of the attribute from the XML data?

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>')

I am expecting the answer to be abc123 & null for this XML. Below is the query I tried to get my expected value.

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)

I am not sure what is missing in my xpath to get the value. Thanks for your help.

CodePudding user response：

This:

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

means that the document has a default namespace, and so all the elements are actually in the namespace. That document is equivilent to this one:

<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>

so you must namespace-qualify your query with WITH XMLNAMESPACES, eg

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)

And you should also fully-qualify the element XPath instead of using // in most cases. EG

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)