問題描述

所以我試圖從 等于 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

(小提琴內(nèi)容)

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

這篇關(guān)于從 XML 字段中選擇 XML 字段 = X的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！