SQL Server 2012 OPENXML-单个级别上的多个属性

Dan 发表于 Dev

和

我是将XML用于SQL的新手，我已经收到一个XML文件，并使用XML数据类型将其移动到表中。我现在正在尝试查询该文件，但我不知道如何在单个级别上带回多个属性。

例如，我在下面包含了XML的摘要，我希望能够在同一行数据上查看担保明细和财务明细，但是如果我导航到担保明细，那么如何获得财务明细？
感谢所有的帮助。担

 DECLARE @XML AS XML, @hDoc AS INT
SELECT @XML =  
'<Deal portal_deal_id="821" revision_id="837" deal_name="Davi Test" bank_deal_id="9812" action="CreateDeal">
  <Application_route>NonATP</Application_route>
  <Application_owner>[email protected]</Application_owner>
  <Application_bank>Santander</Application_bank>
  <Application_status>Submitted</Application_status>
  <Application_group>BSS</Application_group>
 <Facilities>
    <BSS>
      <Guarantee_details>
        <BSS_portal_facility_id>4587</BSS_portal_facility_id>
        <BSS_bank_id>Bank Unique Id</BSS_bank_id>
        <BSS_issuer>Name of bond issuer</BSS_issuer>
        <BSS_stage>Commitment</BSS_stage>
        <BSS_beneficiary>Test beneficiary name</BSS_beneficiary>
      </Guarantee_details>
      <Financial_details>
        <BSS_value>120000.00</BSS_value>
        <BSS_currency>GBP</BSS_currency>
        <BSS_conversion_rate_deal>1.2</BSS_conversion_rate_deal>
        <BSS_conversion_date_deal>25-12-2016</BSS_conversion_date_deal>
        <BSS_fee_rate>1.5</BSS_fee_rate>
        <BSS_fee_perc>10</BSS_fee_perc>
        <BSS_guarantee_perc>25</BSS_guarantee_perc>
        <BSS_max_liability>1200.50</BSS_max_liability>
        <BSS_min_quarterly_fee>1000.00</BSS_min_quarterly_fee>
        <BSS_related_security>525.50</BSS_related_security>
        <BSS_related_security_cur>GBP</BSS_related_security_cur>
      </Financial_details>
    </BSS>
 </Facilities>
</Deal>
'

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT 
portal_deal_id, 
revision_id, 
Deal_name, 
Bank_deal_id, 
[action], 
Application_route,
 Application_owner, 
 Application_bank, 
 Application_status, 
 Application_group
 ,BSS_portal_facility_id



FROM OPENXML(@hDoc, '/Deal/Facilities/BSS/Guarantee_details',3)
WITH 
(
portal_deal_id int '../../../@portal_deal_id',
revision_id int '../../../@revision_id',
deal_name [varchar](50) '../../../@deal_name',
bank_deal_id [varchar](50) '../../../@bank_deal_id',
[action] [varchar](50) '../../../@action',
Application_route [varchar](50)  '../../../Application_route/text()',
 Application_owner [varchar](50) '../../../Application_owner/text()',
 Application_bank [varchar](50)  '../../../Application_bank/text()',
 Application_status [varchar](50) '../../../Application_status/text()',
 Application_group [varchar](50) '../../../Application_group/text()'
 ,BSS_portal_facility_id [varchar](50) 'BSS_portal_facility_id/text()'
)
EXEC sp_xml_removedocument @hDoc
GO

塞格

使用xml数据类型方法代替OPENXML。例如

DECLARE @XML AS XML;
SELECT @XML =  
'<Deal portal_deal_id="821" revision_id="837" deal_name="Davi Test" bank_deal_id="9812" action="CreateDeal">
  <Application_route>NonATP</Application_route>
  <Application_owner>[email protected]</Application_owner>
  <Application_bank>Santander</Application_bank>
  <Application_status>Submitted</Application_status>
  <Application_group>BSS</Application_group>
 <Facilities>
    <BSS>
      <Guarantee_details>
        <BSS_portal_facility_id>4587</BSS_portal_facility_id>
        <BSS_bank_id>Bank Unique Id</BSS_bank_id>
        <BSS_issuer>Name of bond issuer</BSS_issuer>
        <BSS_stage>Commitment</BSS_stage>
        <BSS_beneficiary>Test beneficiary name</BSS_beneficiary>
      </Guarantee_details>
      <Financial_details>
        <BSS_value>120000.00</BSS_value>
        <BSS_currency>GBP</BSS_currency>
        <BSS_conversion_rate_deal>1.2</BSS_conversion_rate_deal>
        <BSS_conversion_date_deal>25-12-2016</BSS_conversion_date_deal>
        <BSS_fee_rate>1.5</BSS_fee_rate>
        <BSS_fee_perc>10</BSS_fee_perc>
        <BSS_guarantee_perc>25</BSS_guarantee_perc>
        <BSS_max_liability>1200.50</BSS_max_liability>
        <BSS_min_quarterly_fee>1000.00</BSS_min_quarterly_fee>
        <BSS_related_security>525.50</BSS_related_security>
        <BSS_related_security_cur>GBP</BSS_related_security_cur>
      </Financial_details>
    </BSS>
 </Facilities>
</Deal>
';

select t.n.value('(Application_route)[1]','varchar(50)') as Application_route,
    t.n.value('@revision_id', 'int') as revision_id,
    f.n.value('(Guarantee_details/BSS_portal_facility_id)[1]','varchar(50)') as BSS_portal_facility_id,
    f.n.value('(Financial_details/BSS_value)[1]','varchar(50)') as BSS_value
from @XML.nodes('Deal') t(n)
cross apply t.n.nodes('Facilities/BSS') f(n);

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。