Home » SQL & PL/SQL » SQL & PL/SQL » How to print empty tag if value does not exist (oracle 11g)
How to print empty tag if value does not exist [message #628710] Thu, 27 November 2014 05:12 Go to next message
b_chugh
Messages: 68
Registered: August 2005
Location: delhi
Member
Hi All

The below query does not add AddrLine tag if there is no address line exist in AddrLine table for an address. I want to still add an empty tag for AddrLine. Please suggest.


SELECT
XMLCONCAT(
XMLELEMENT ( "Collateral", XMLATTRIBUTES (P.PropNbr AS "PropNbr",NULL AS "DepAcctNbr", NULL AS "DepMajorDesc", NULL AS "DepMinorDesc") ,
XMLELEMENT ( "CollateralType", P.PROPTYPCD ),
XMLELEMENT ( "Make", P.PROPMAKE ),
XMLELEMENT ( "Model", P.PROPMODEL ),
XMLELEMENT ( "Year", P.PROPYEARNBR ),
XMLELEMENT ( "CollateralDesc", P.PROPDESC ),
XMLELEMENT ("CollateralAddress",
XMLCONCAT( ( SELECT XMLAGG( XMLELEMENT("AddrLine",XMLATTRIBUTES (AL.ADDRLINETYPCD AS "AddrLineTypCd",AL.Text AS "Text" )))
FROM AddrLine AL
WHERE AL.AddrNbr = D.AddrNbr
),
XMLElement("CityName", D.CityName),
XMLElement("StateCd", D.StateCd),
XMLElement("ZipCd", D.ZipCd),
XMLElement("ZipSuf", D.ZipSuf),
XMLElement("CtryCD", D.CtryCd),
XMLElement("MailCd", D.CtryMailCd),
XMLElement("CtrySubDivCd", D.CtrySubDivCd))))) Address
FROM Acct AA
LEFT OUTER JOIN AcctProp AP on AP.AcctNbr = AA.AcctNbr
LEFT OUTER JOIN Prop P on P.PropNbr = AP.PropNbr
LEFT OUTER JOIN Addr D on D.addrnbr = P.AddrNbr
WHERE AA.AcctNbr = cpnAcctNbr
AND AP.EffDate <= cpdEffDate
AND (AP.InactiveDate IS NULL OR AP.InactiveDate > cpdStartDate)
ORDER BY AP.PropNbr


Thank You!!
b_chugh
Re: How to print empty tag if value does not exist [message #628713 is a reply to message #628710] Thu, 27 November 2014 05:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Maybe you have to use an OUTER JOIN.
If you post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.
Re: How to print empty tag if value does not exist [message #628725 is a reply to message #628710] Thu, 27 November 2014 07:12 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Check it out.

SY.
Re: How to print empty tag if value does not exist [message #628925 is a reply to message #628725] Mon, 01 December 2014 00:29 Go to previous messageGo to next message
b_chugh
Messages: 68
Registered: August 2005
Location: delhi
Member
I added NVL and it worked. Although I was not able to add empty attributes but I think it will do.

XMLELEMENT ("CollateralAddress",
XMLCONCAT( NVL(( SELECT XMLAGG( XMLELEMENT("AddrLine",XMLATTRIBUTES (AL.ADDRLINETYPCD AS "AddrLineTypCd",AL.Text AS "Text" )))
FROM AddrLine AL
WHERE AL.AddrNbr = D.AddrNbr
),XMLELEMENT ( "AddrLine")),


Thanks everyone.
Re: How to print empty tag if value does not exist [message #628930 is a reply to message #628925] Mon, 01 December 2014 02:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I doubt this will work as NVL requires 2 parameters.

Re: How to print empty tag if value does not exist [message #628935 is a reply to message #628930] Mon, 01 December 2014 05:28 Go to previous messageGo to next message
b_chugh
Messages: 68
Registered: August 2005
Location: delhi
Member
It is working like NVL(a,b). The inner select is parameter a to NVL and XMLELEMENT ( "AddrLine") is working as parameter b in the NVL. So it is working.

Thank You!!
Re: How to print empty tag if value does not exist [message #628939 is a reply to message #628935] Mon, 01 December 2014 06:04 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Ah OK, expression not complete and well formatted so I miss some parentheses. Smile

Previous Topic: Concate 2 table columns
Next Topic: Merge BLOB
Goto Forum:
  


Current Time: Thu Mar 28 05:12:05 CDT 2024