sql-server - 如何获取 XML 节点值的部分值

Question

我是 Xpath 的新手，这是我的 XML。我正在尝试获取 appl/*__job 标记中的属性值 @name 和节点 snmp_notify/message 中的值 'TESTQUEUE '，我一次迈出一步。到目前为止，我能够获取所有_job的子节点，但无法获取节点/snmp_notifylist/snmp_notify/message中的值。这是 SQL，有人可以帮我确定我卡在哪里。

这是在表 TAB_AR 中存储为 DEFINITION 的示例 XML 文档。

<appl xmlns="http://dto.wa.ca.com/application" name="TEST_NEW_AGENT">
   <version>12.0</version>
   <comment />
   <unix_job name="TEST_JOB">
     <dependencies><relcount>0</relcount></dependencies>
      <snmp_notifylist>
       <snmp_notify>
         <returncode>4</returncode>
           <monitor_states><monitor_state>FAILED</monitor_state></monitor_states>
           <snmpagent />
           <message>TICKET TESTQUEUE TSTMSG</message>
      </snmp_notify>
   </snmp_notifylist>
 </unix_job>
 <link name="HOLD_LINK">
    <dependencies><relcount>0</relcount></dependencies>
    <hold>true</hold>
   <job_ancestor_wait_default_ignore>true</job_ancestor_wait_default_ignore>
 </link>
 <sftp_job name="TEST_SFTP1">
    <dependencies><relcount>0</relcount></dependencies>
    <snmp_notifylist>
         <snmp_notify>
           <returncode>4</returncode>
             <monitor_states>
                 <monitor_state>FAILED</monitor_state>
             </monitor_states>
            <snmpagent />
            <message>TICKET MFG1AWA TSTMSG</message>
            </snmp_notify>
    </snmp_notifylist>
</sftp_job>
</appl>

这是我写的SQL，

 SELECT  
 SFTP_Job_name = DEFT1.value('(@name)[1]','nvarchar(max)'),
 Server_Address = DEFT1.query('local-name(/*:snmp_notifylist/*:snmp_notify/*:message)')
 from (select CAST([DEFINITION] as XML) as DEFT from TAB_AR)TAB
 CROSS APPLY TAB.DEFT.nodes('/*:appl/*[fn:contains(local-name(),"_job")]') as XMLTAB1(DEFT1)

Answer 1

你很亲近...
在这一行中，我不确定您真正想要得到什么:

DEFT1.query('local-name(/*:snmp_notifylist/*:snmp_notify/*:message)')

与 local-name()您可以返回一个特定节点的名称。当您从几个以 _job 结尾的节点读取数据时返回您正在读取的元素的名称非常有意义。
但是您告诉我们，您正在尝试阅读 <message>也。可能是，你在一条线上混合了两个电话？
我稍微修改了你的代码:

SELECT 
  SFTP_Job_name = DEFT1.value('(@name)[1]','nvarchar(max)')
 ,NodeName = DEFT1.value('local-name(.)','nvarchar(max)')
 ,Server_Address = DEFT1.value('(*:snmp_notifylist/*:snmp_notify/*:message)[1]','nvarchar(max)')
 from (select CAST([DEFINITION] as XML) as DEFT from TAB_AR)TAB
 CROSS APPLY TAB.DEFT.nodes('/*:appl/*[fn:contains(local-name(.),"_job")]') as XMLTAB1(DEFT1);

这返回

SFTP_Job_name   NodeName    Server_Address
TEST_SFTP1      sftp_job    TICKET MFG1AWA TSTMSG
TEST_JOB        unix_job    TICKET TESTQUEUE TSTMSG

就像 Roger Wolf 指出的那样，最好使用这样的指定 namespace 进行阅读:

 WITH XMLNAMESPACES (default 'http://dto.wa.ca.com/application')
 SELECT 
  SFTP_Job_name = DEFT1.value('(@name)[1]','nvarchar(max)')
 ,NodeName = DEFT1.value('local-name(.)','nvarchar(max)')
 ,Server_Address = DEFT1.value('(snmp_notifylist/snmp_notify/message)[1]','nvarchar(max)')
 from (select CAST([DEFINITION] as XML) as DEFT from TAB_AR)TAB
 CROSS APPLY TAB.DEFT.nodes('/appl/*[fn:contains(local-name(.),"_job")]') as XMLTAB1(DEFT1);

一般规则是:尽可能具体!
暗示
如果可以更改此设置，则应将 XML 存储在类型为 XML 的列中。 .
本次 build from (select CAST([DEFINITION] as XML) as DEFT from TAB_AR)TAB应该真的没必要……
可能是，您的专栏是 XML实际上，您只是不知道如何将找到的代码转移到某处以获得 .nodes() 的正确语法。 ?在这种情况下，试试这个:

SELECT 
  SFTP_Job_name = DEFT1.value('(@name)[1]','nvarchar(max)')
 ,NodeName = DEFT1.value('local-name(.)','nvarchar(max)')
 ,Server_Address = DEFT1.value('(*:snmp_notifylist/*:snmp_notify/*:message)[1]','nvarchar(max)')
 from TAB_AR
 CROSS APPLY TAB_AR.[DEFINITION].nodes('/*:appl/*[fn:contains(local-name(.),"_job")]') as XMLTAB1(DEFT1);