从 XML 字段中选择 XML 字段 = X

问题描述

所以我试图从 等于 Payment method 的 SQL Server 表中选择每条记录，但我想要 的值代码>.我似乎无法得到任何接近我想要的东西.有什么想法吗?

So I am trying to select each record out of a SQL Server table that has a <FIELD> equal to Payment method but I want the value of the <NEWVALUE>. I can't seem to get anything even close to what I want. Any ideas?

http://sqlfiddle.com/#!3/7c0a0/1/0

(小提琴内容)

create table T1(ID int identity(1, 1), XMLFIELD xml)
insert into T1(XMLFIELD) values('<ITEMS>
  <ITEM>
    <PROCESS>True</PROCESS>
    <FIELD>Amount</FIELD>
    <NEWVALUE>100</NEWVALUE>
    <ACTION>0</ACTION>
    <FIELDKEY>AMOUNT</FIELDKEY>
    <DATATYPE>4</DATATYPE>
  </ITEM>  
  <ITEM>
    <PROCESS>True</PROCESS>
    <FIELD>Payment method</FIELD>
    <NEWVALUE>Credit card</NEWVALUE>
    <ACTION>0</ACTION>
    <FIELDKEY>PAYMENTMETHODCODE</FIELDKEY>
    <DATATYPE>1</DATATYPE>
  </ITEM>  
</ITEMS>')
insert into T1(XMLFIELD) values('<ITEMS>
  <ITEM>
    <PROCESS>True</PROCESS>
    <FIELD>Amount</FIELD>
    <NEWVALUE>25</NEWVALUE>
    <ACTION>0</ACTION>
    <FIELDKEY>AMOUNT</FIELDKEY>
    <DATATYPE>4</DATATYPE>
  </ITEM>  
  <ITEM>
    <PROCESS>True</PROCESS>
    <FIELD>Payment method</FIELD>
    <NEWVALUE>Debit</NEWVALUE>
    <ACTION>0</ACTION>
    <FIELDKEY>PAYMENTMETHODCODE</FIELDKEY>
    <DATATYPE>2</DATATYPE>
  </ITEM>  
</ITEMS>')

推荐答案

试试这个:

SELECT
    ID,
    NewValue = XItem.value('(NEWVALUE)[1]', 'varchar(50)')
FROM 
    T1
CROSS APPLY 
    XmlField.nodes('/ITEMS/ITEM') as XTbl(XItem)
WHERE
    XItem.exist('FIELD[.="Payment method"]') = 1