对列的Server查询值
对列的Server查询值
提问于 2019-09-03 15:52:45
我正试图将一张XML文档表分解到Server列中，然后绕着一圈转一圈。
基本上，我有一个表(ID int, XMLData XML)，每一行都将在XML列中包含一个文档。
我需要将它转换成以下格式
(
ID int,
ReferenceCurrency varchar,
TargetCurrency varchar,
ReferenceAmount decimal,
TargetAmount decimal,
DueDate date
)我把这张桌子砍掉了。
我继承的代码是XML.value和string搜索与charindex的混合，而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我要解决的问题是如何获得CcyIndic = reference_currency或target_currency的两列。
在本例中，我希望得到1，英镑，美元，65.62，96.62,2019-09-04的单行。
任何帮助都很感激。
回答 2
Stack Overflow用户
回答已采纳
发布于 2019-09-03 16:59:57
SQL
-- DDL and sample data population, start
DECLARE @tbl TABLE(ID INT IDENTITY(1,1) PRIMARY KEY, XMLData XML);
INSERT INTO @tbl
VALUES
(N'<Jv-Ins-Reinsurance>
<TechAccount>
<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>
</TechAccount>
</Jv-Ins-Reinsurance>');
-- DDL and sample data population, end
SELECT ID,
c.value('(ReferenceCurrency/Ccy)[1]', 'varchar(4)') AS ReferenceCurrency ,
c.value('(TargetCurrency/Ccy)[1]', 'varchar(4)') AS TargetCurrency,
c.value('(BalanceAmtItem/Amt[@CcyIndic="reference_currency"])[1]', 'MONEY') AS ref_money,
c.value('(BalanceAmtItem/Amt[@CcyIndic="target_currency"])[1]', 'MONEY') AS target_money,
c.value('(BalanceAmtItem/DueDate)[1]', 'date') AS DueDate
FROM @tbl AS tbl
CROSS APPLY tbl.xmldata.nodes('/Jv-Ins-Reinsurance/TechAccount') AS t(c);Stack Overflow用户
发布于 2019-09-03 16:50:46
就像这样：
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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/57775076
复制相关文章
相似问题
腾讯云开发者
