TSQL xml查询 [英] TSQL xml query

问题描述

我的表格中有xml类型的列。我存储了xml消息。我需要查询整个表的特定值。

My table has column of type xml. I stored xml messages. I need to query entire table for particular value.

我该怎么做。

这是xml消息，如下所示：

Here is xml message that looks like:

我需要得到ID = P137605的所有行。我们怀疑可能有多行具有该值。 

I need to get all rows where ID =P137605. We are suspecting there may be multiple rows with that value. 

谢谢

推荐答案

你可以利用value（）方法解析ID值并将其用作过滤谓词。

you can leverage value() method to parse ID value and use that as filter predicate.

根据你的示例数据，我创建了一个有三行的表。第（1）和（3）行的ID = P137605  and row（2）有ID P137606

Taking your example data, I have created a table with three rows. Row (1) and (3) have ID = P137605  and row (2) has ID P137606

CREATE TABLE xml_table (id int, xml_data xml);

INSERT INTO xml_table 
VALUES
(1,N'<MESSAGE MessageID =''S001'' MessageCreationTime =''2019-06-04T15:15:55.667'' AppName=''ACT''>
  <TRANSACTIONS ChildCount=''1''>
    <TRANSACTION>
      <ACTION>Add</ACTION>
      <BUS_CODE>Deposit</BUS_CODE>
      <CATEGORY>Savings</CATEGORY>
      <DEPOSIT_DATE>2019-06-05</DEPOSIT_DATE>
      <APPROVAL_DATE>2019-06-05</APPROVAL_DATE>      
      <ID>P137605</ID>      
      <RECEIVED>Y</RECEIVED>
      <ACCOUNTS ChildCount=''1''>
        <ACCOUNT>
          <DEBIT_ACCOUNT>1101</DEBIT_ACCOUNT>
          <DEBIT_SUBACCOUNT>00-1101</DEBIT_SUBACCOUNT>
          <CREDIT_ACCOUNT>1002</CREDIT_ACCOUNT>
          <CREDIT_SUBACCOUNT>11-1002</CREDIT_SUBACCOUNT>
          <AMOUNT>100.0000</AMOUNT>          
        </ACCOUNT>
      </ACCOUNTS>
    </TRANSACTION>
  </TRANSACTIONS>
</MESSAGE>')
,(2,N'<MESSAGE MessageID =''S002'' MessageCreationTime =''2019-06-04T15:15:55.667'' AppName=''ACT''>
  <TRANSACTIONS ChildCount=''1''>
    <TRANSACTION>
      <ACTION>Add</ACTION>
      <BUS_CODE>Deposit</BUS_CODE>
      <CATEGORY>Savings</CATEGORY>
      <DEPOSIT_DATE>2019-06-05</DEPOSIT_DATE>
      <APPROVAL_DATE>2019-06-05</APPROVAL_DATE>      
      <ID>P137606</ID>      
      <RECEIVED>Y</RECEIVED>
      <ACCOUNTS ChildCount=''1''>
        <ACCOUNT>
          <DEBIT_ACCOUNT>1101</DEBIT_ACCOUNT>
          <DEBIT_SUBACCOUNT>00-1101</DEBIT_SUBACCOUNT>
          <CREDIT_ACCOUNT>1002</CREDIT_ACCOUNT>
          <CREDIT_SUBACCOUNT>11-1002</CREDIT_SUBACCOUNT>
          <AMOUNT>100.0000</AMOUNT>          
        </ACCOUNT>
      </ACCOUNTS>
    </TRANSACTION>
  </TRANSACTIONS>
</MESSAGE>')
,(3,N'<MESSAGE MessageID =''S003'' MessageCreationTime =''2019-06-04T15:15:55.667'' AppName=''ACT''>
  <TRANSACTIONS ChildCount=''1''>
    <TRANSACTION>
      <ACTION>Add</ACTION>
      <BUS_CODE>Deposit</BUS_CODE>
      <CATEGORY>Savings</CATEGORY>
      <DEPOSIT_DATE>2019-06-05</DEPOSIT_DATE>
      <APPROVAL_DATE>2019-06-05</APPROVAL_DATE>      
      <ID>P137605</ID>      
      <RECEIVED>Y</RECEIVED>
      <ACCOUNTS ChildCount=''1''>
        <ACCOUNT>
          <DEBIT_ACCOUNT>1101</DEBIT_ACCOUNT>
          <DEBIT_SUBACCOUNT>00-1101</DEBIT_SUBACCOUNT>
          <CREDIT_ACCOUNT>1002</CREDIT_ACCOUNT>
          <CREDIT_SUBACCOUNT>11-1002</CREDIT_SUBACCOUNT>
          <AMOUNT>100.0000</AMOUNT>          
        </ACCOUNT>
      </ACCOUNTS>
    </TRANSACTION>
  </TRANSACTIONS>
</MESSAGE>');

要选择ID = P137605的所有行，我使用以下查询：

To select all rows where ID = P137605, I used below query:

SELECT *
FROM xml_table
WHERE xml_data.value('(MESSAGE/TRANSACTIONS/TRANSACTION/ID)[1]', 'VARCHAR(100)') = 'P137605';

希望这就是你要找的东西！

Hope this is what you are looking for!

这篇关于TSQL xml查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！