从 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
相关文章