Hi All,
I need to add text for nodes which are empty. For example: In the below XML document PD1_UPC_CASE has no text, <PD1_UPC_CASE Error_Flag="N"/>. I need to add an text as "Data is Missing" when no text and also update the attribute @Error_Flag to 'Y' when its empty.
I used the below query to update the attribute, but it updates for all PD1_UPC_CASE elements. I am stuck with this and need help.
UPDATE appowner.xml_doc
SET xml_data = UPDATEXML (xml_data, '//PD1/PD1_UPC_CASE/@Error_Flag', 'Y')
WHERE EXISTSNODE (xml_data, '//PD1/PD1_UPC_CASE[not(text())= 1;
<?xml version="1.0"?>
<ROWSET>
<ROW>
<XML_DATA>
<PH1>
<PH1_RECORD_ID Error_Flag="N">PH1</PH1_RECORD_ID>
<PH1_DOC_TYPE Error_Flag="N">PRC</PH1_DOC_TYPE>
<PH1_VER_NUM Error_Flag="N">22</PH1_VER_NUM>
<PD1>
<PD1_RECORD_ID Error_Flag="N">PD1</PD1_RECORD_ID>
<PD1_MAIN_TYPE Error_Flag="N">003</PD1_MAIN_TYPE>
<PD1_DATE_QUAL Error_Flag="N">07</PD1_DATE_QUAL>
<PD1_DATE Error_Flag="N">20160926</PD1_DATE>
<PD1_UPC_CASE Error_Flag="N">736920111846</PD1_UPC_CASE>
<PD1_UPCEAN_PC Error_Flag="N">736920111822</PD1_UPCEAN_PC>
</PD1>
<PD1>
<PD1_RECORD_ID Error_Flag="N">PD1</PD1_RECORD_ID>
<PD1_MAIN_TYPE Error_Flag="N">003</PD1_MAIN_TYPE>
<PD1_DATE_QUAL Error_Flag="N">07</PD1_DATE_QUAL>
<PD1_DATE Error_Flag="N">20160801</PD1_DATE>
<PD1_UPC_CASE Error_Flag="N">736920111945</PD1_UPC_CASE>
<PD1_UPCEAN_PC Error_Flag="N">736920111945</PD1_UPCEAN_PC>
</PD1>
<PD1>
<PD1_RECORD_ID Error_Flag="N">PD1</PD1_RECORD_ID>
<PD1_MAIN_TYPE Error_Flag="N">003</PD1_MAIN_TYPE>
<PD1_DATE_QUAL Error_Flag="N">07</PD1_DATE_QUAL>
<PD1_DATE Error_Flag="N">20160926</PD1_DATE>
<PD1_UPC_CASE Error_Flag="N"/>
<PD1_UPCEAN_PC Error_Flag="N">736920111327</PD1_UPCEAN_PC>
</PD1>
<PD1>
<PD1_RECORD_ID Error_Flag="N">PD1</PD1_RECORD_ID>
<PD1_MAIN_TYPE Error_Flag="N">003</PD1_MAIN_TYPE>
<PD1_DATE_QUAL Error_Flag="N">07</PD1_DATE_QUAL>
<PD1_DATE Error_Flag="N">20160926</PD1_DATE>
<PD1_UPC_CASE Error_Flag="N"/>
<PD1_UPCEAN_PC Error_Flag="N">736920111808</PD1_UPCEAN_PC>
</PD1>
</PH1>
</XML_DATA>
</ROW>
</ROWSET>