sql-server - SQL Server XML 查询值到列

Question

我正在尝试将 XML 文档表分解为 SQL Server 列，并且我正在绕圈子。

基本上我有一个表 ( ID int, XMLData XML) 并且每一行都将包含 XML 列中的一个文档。

我需要把它变成以下格式

(
ID int, 
ReferenceCurrency varchar, 
TargetCurrency varchar, 
ReferenceAmount decimal, 
TargetAmount decimal, 
DueDate date
)

我把这张桌子砍了。

我继承的代码是 XML.value 和字符串搜索与 charindex 的混合，这不太可靠。

这是我们收到的信息的一个片段。

<ReferenceCurrency>
      <Ccy>GBP</Ccy>
</ReferenceCurrency>
<TargetCurrency>
      <Ccy>USD</Ccy>
</TargetCurrency>    
<BalanceAmtItem Type="technical_account_settlement_balance_due_to_sender">
      <Amt Ccy="USD" CcyIndic="reference_currency" Share="receiver_share">65.62</Amt>
      <Amt Ccy="USD" CcyIndic="target_currency" Share="receiver_share">96.62</Amt>
      <DueDate>2019-09-04</DueDate>
</BalanceAmtItem>

我可以使用 XML.value 获取大部分数据

SELECT 
    ID, 
    XMLDATA.value('(Jv-Ins-Reinsurance/TechAccount/ReferenceCurrency)[1]', 'varchar(4)') AS ReferenceCurrency ,
    XMLDATA.value('(Jv-Ins-Reinsurance/TechAccount/TargetCurrency)[1]', 'varchar(4)') AS TargetCurrency,
    XMLDATA.value('(Jv-Ins-Reinsurance/TechAccount/BalanceAmtItem/DueDate)[1]', 'date') AS DueDate
FROM
    dta

我正在努力解决的问题是如何获得 CcyIndi​​c = reference_currency 或 target_currency 的两个金额列。

在这个例子中，我希望得到一行 1, GBP, USD, 65.62, 96.62, 2019-09-04

任何帮助表示赞赏。

标签： sql-serverxml

Answer 1

像这样的东西：

declare @doc xml = 
'<ReferenceCurrency>
      <Ccy>GBP</Ccy>
</ReferenceCurrency>
<TargetCurrency>
      <Ccy>USD</Ccy>
</TargetCurrency>    
<BalanceAmtItem Type="technical_account_settlement_balance_due_to_sender">
      <Amt Ccy="USD" CcyIndic="reference_currency" Share="receiver_share">65.62</Amt>
      <Amt Ccy="USD" CcyIndic="target_currency" Share="receiver_share">96.62</Amt>
      <DueDate>2019-09-04</DueDate>
</BalanceAmtItem>';

SELECT 
   -- ID, 
    XMLDATA.value('(/ReferenceCurrency)[1]', 'varchar(4)') AS ReferenceCurrency,
    XMLDATA.value('(/TargetCurrency)[1]', 'varchar(4)') AS TargetCurrency,
    XMLDATA.value('(/BalanceAmtItem/Amt[@CcyIndic="reference_currency"])[1]', 'varchar(4)') AS ReferenceAmount ,
    XMLDATA.value('(/BalanceAmtItem/Amt[@CcyIndic="target_currency"])[1]', 'varchar(4)') AS TargetAmount ,
    XMLDATA.value('(/BalanceAmtItem/DueDate)[1]', 'date') AS DueDate
FROM
    (select @doc XMLDATA) d

输出

ReferenceCurrency TargetCurrency ReferenceAmount TargetAmount DueDate
----------------- -------------- --------------- ------------ ----------
GBP               USD            65.6            96.6         2019-09-04