使用 SQL 删除 XML 文件中的空节点 [英] Delete empty node in XML file using SQL

问题描述

您好，我在 SQL 中使用 XML Auto 创建了 XML 文件.该文件是根据六个表中的记录创建的.如果关联表中没有值，则创建一个空节点.有没有办法删除SSIS或SQL中的空节点?

Hi i have created XML file using XML Auto in SQL. The file is created based on the records from six tables. If there is no value in associated table, an empty node is created. Is there a way to delete a empty node in SSIS or SQL?

下面是使用的查询

select
(select 

      Opp.OPPORTUNITYID as OPPORTUNITYID,
      Opp.CREATEDATE as RequestDate,
      (select USERNAME from sysdba.USERINFO where USERID=Opp.ACCOUNTMANAGERID) as RepName,
      (select Account from sysdba.ACCOUNT where ACCOUNTID=Opp.ACCOUNTID )as Account,
      (select ACCOUNTID from sysdba.ACCOUNT where ACCOUNTID=Opp.ACCOUNTID ) as AccountID,
      (select MDRPID from sysdba.ACCOUNT where ACCOUNTID=Opp.ACCOUNTID ) as MDRPID,
      (select Coalesce(acc1.accountid,acc.accountid) from sysdba.ACCOUNT  acc  left  join sysdba.ACCOUNT acc1 on acc.UNIVERSITYID = acc1.accountid where acc.ACCOUNTID=Opp.ACCOUNTID ) as UniversityID,
      (select Coalesce(acc1.account,acc.account) from sysdba.ACCOUNT  acc  left  join sysdba.ACCOUNT acc1 on acc.UNIVERSITYID = acc1.accountid where acc.ACCOUNTID=Opp.ACCOUNTID ) as University,
      (Opp.STARTDATE) as StartDate,
      (opp.enddate) as EndDate,
     Coalesce ( opp.PROMOCODE,'') as Promocode,
      Opp.MICROSITEURLKEY as MicrositeURLKey,
      opp.modifydate as ModifyDate,
      (select GUID from sysdba.ACCOUNT where ACCOUNTID=Opp.ACCOUNTID ) as LocationID,
      OppProduct.OPPPRODUCTID as OppProductID,
        OppProduct.PRICE as Price,
      OppProduct.CALCPRICE as CalculatedPrice,
      OppProduct.discount as Discount,
     (select pro.ISBN from sysdba.C_PRODUCT pro join sysdba.OPPORTUNITY_PRODUCT opppro on pro.C_PRODUCTID=opppro.PRODUCTID where opppro.OPPPRODUCTID=OppProduct.OPPPRODUCTID ) as ISBN,
      (select pro.TITLE from sysdba.C_PRODUCT pro join sysdba.OPPORTUNITY_PRODUCT opppro on pro.C_PRODUCTID=opppro.PRODUCTID where opppro.OPPPRODUCTID=OppProduct.OPPPRODUCTID ) as Title,
      AccCourse.ACCOUNTCOURSEID  AS ACCOUNTCOURSEID,
      AccCourse.COURSENAME as CourseName,
      AccCourse.MDRCLGCOURSE as CLGCourse,
      Contact.CONTACTID  as CONTACTID,
      Contact.FIRSTNAME as FirstName,
      Contact.LASTNAME as LastName,
      Contact.EMAIL as Email


from sysdba.OPPORTUNITY Opp
 join sysdba.OPPORTUNITY_PRODUCT OppProduct on Opp.OPPORTUNITYID = OppProduct.OPPORTUNITYID
left join sysdba.C_PRODUCT Product on OppProduct.PRODUCTID = Product.C_PRODUCTID
left join sysdba.OPPPRODUCTCOURSE ProductCourse on OppProduct.OPPPRODUCTID = ProductCourse.OPPPRODUCTID
left join sysdba.OPPORTUNITYCOURSE OppCourse on ProductCourse.OPPORTUNITYCOURSEID = OppCourse.OPPORTUNITYCOURSEID
left join sysdba.ACCOUNTCOURSE AccCourse on OppCourse.ACCOUNTCOURSEID = AccCourse.ACCOUNTCOURSEID
left join sysdba.OPPCOURSECONTACT OppContact on OppCourse.OPPORTUNITYCOURSEID = OppContact.OPPORTUNITYCOURSEID
left join sysdba.CONTACT contact on contact.CONTACTID=OppContact.CONTACTID
For XML AUTO, ROOT('INFO'))as col

输出:

<INFO>
  <Opp LocationID="1594743" ModifyDate="2015-04-01T23:50:01" MicrositeURLKey="O6UJ9A03XT6J" MDRPID="1594743" AccountID="A6UJ9A00OS1L" Account="Marquette University " RepName="Maggie Dolan" RequestDate="2015-04-01T23:48:07" OPPORTUNITYID="O6UJ9A03XT6J">
    <OppProduct Title="prepU for Jensen's Nursing Health Assessment: A Best Practice Approach, Ecommerce, 3 Month Access (PREPU)" ISBN="9781469895727" Price="0.0000" OppProductID="Q6UJ9A15NMSF">
      <AccCourse>
        <contact />
      </AccCourse>
    </OppProduct>
    <OppProduct Title="Lippincott CoursePoint for Nursing Health Assessment: A Best Approach, Ecommerce Version, 12 Month Access (CoursePoint)" ISBN="9781496303134" Price="0.0000" OppProductID="Q6UJ9A15NMSG">
      <AccCourse>
        <contact />
      </AccCourse>
    </OppProduct>
  </Opp>
</INFO>

如果节点为空，有没有办法删除它?例如 和 .

Is there a way to remove the node if its empty? For eg <Contact> and <AccCourse>.

推荐答案

你可以像这样使用一行代码

you can use the Line of code like this

    SET @testXML.modify('delete  /INFO//*[not(@*)]')

这将删除 INFO 下没有任何属性的所有节点.

This will remove all the nodes under INFO which do not have any attributes.

完整代码示例

    DECLARE @testXML XML
    SET @testXML=            
                   ' <INFO>
      <Opp LocationID="1594743" ModifyDate="2015-04-01T23:50:01" MicrositeURLKey="O6UJ9A03XT6J" MDRPID="1594743" AccountID="A6UJ9A00OS1L" Account="Marquette University " RepName="Maggie Dolan" RequestDate="2015-04-01T23:48:07" OPPORTUNITYID="O6UJ9A03XT6J">
        <OppProduct Title="prepU for Jensen''s Nursing Health Assessment: A Best Practice Approach, Ecommerce, 3 Month Access (PREPU)" ISBN="9781469895727" Price="0.0000" OppProductID="Q6UJ9A15NMSF">
          <AccCourse>
            <contact />
          </AccCourse>
        </OppProduct>
        <OppProduct Title="Lippincott CoursePoint for Nursing Health Assessment: A Best Approach, Ecommerce Version, 12 Month Access (CoursePoint)" ISBN="9781496303134" Price="0.0000" OppProductID="Q6UJ9A15NMSG">
          <AccCourse>
            <contact />
          </AccCourse>
        </OppProduct>
      </Opp>
    </INFO>'
    select @testXML 
    SET @testXML.modify('delete  /INFO//*[not(@*)]')
    select @testXML

这篇关于使用 SQL 删除 XML 文件中的空节点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！