所以我试图从 SQL Server 表中选择每条记录<FIELD>
等于Payment method
但我想要的值<NEWVALUE>
。我似乎无法得到任何接近我想要的东西。有任何想法吗?
http://sqlfiddle.com/#!3/7c0a0/1/0 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
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)