Delete empty node in XML file using SQL

you can use the Line of code like this

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

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

Complete Code Example

    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

you can use the XML modify() to delete the nodes which do not have an attribute . Something like this.

DECLARE @xml xml =
'<?xml version="1.0"?>
<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 ACCOUNTCOURSEID="2"><contact/></AccCourse></OppProduct></Opp>'

SELECT @xml

SET @xml.modify('delete //AccCourse[empty(@ACCOUNTCOURSEID)]')

SELECT @xml

For more information refer delete (XML DML)

Building upon Arun Gairola answer:

you can use the Line of code like this

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

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

Since your XML is generated from a query, you can set the result into a variable and then delete the empty nodes from the variable. Using your SQL above, you'd want to do something like this:

set @xml xml;
set @xml = (
    select <columns>
    from <tables>
    For XML AUTO, ROOT('INFO')
    )

set @xml.modify('delete  /INFO//*[not(@*)]')
select @xml

Using the complete sql you provided in your question, you'd want to do this:

set @xml xml;
set @xml = (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'))

set @xml.modify('delete  /INFO//*[not(@*)]')
select @xml

I think this one is better:

modify('delete  //*[not(node())]')

You can also refer to this: Delete Empty XML nodes