How to print empty tag if value does not exist [message #628710] |
Thu, 27 November 2014 05:12 |
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 #628925 is a reply to message #628725] |
Mon, 01 December 2014 00:29 |
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.
|
|
|
|
|
|