CODEWITHSUNDEEP
sqlxmloracle-database
Shaun Kinnair
Shaun Kinnair

Reputation: 535

Getting SQL XML tags to show with NULL values

I'm trying to get XML tags to show if no values exist. The XML tags are mandatory in the XML schema file. But in certain instances the values for this Element (tag) do not exist. The actual Oracle11g SQL statement is below (but with a non database value of 9999999999). This Oracle XML SQL query is embedded in other XML SQL.

How would I get the XML tags to show like the below, thanks in advance

<ProdId>
    <idType></idType>
    <id></id>
</ProdId>

SELECT XMLELEMENT ("prodId",   
                  XMLELEMENT ("idType",NVL(x.idType,'N/A')),
                  XMLELEMENT ("id",NVL(x.id,'N/A'))
                  )
 from
    (SELECT  DECODE (alternative_creation_ids.gc_alt_usage_id_type_code,'PROD',1,
                                                'ISAN',2,
                                                'CUI',3,9) AS oderby,
             DECODE(alternative_creation_ids.gc_alt_usage_id_type_code,'PROD','SOCIETY-ID',
                                                                       'ISAN','ISAN',
                                                                        'CUI','CLOCKID','N/A') AS idType,
                                                                   alternative_creation_ids.alternative_creation_id AS id,
                                                                   alternative_creation_ids.cre_surr_id AS cre_surr_id                                                
    FROM    alternative_creation_ids
    WHERE  alternative_creation_ids.gc_alt_usage_id_type_table =  'AUIT'
    AND alternative_creation_ids.gc_alt_usage_id_type_code in ('ISAN','CUI','PROD')
    order by 1) x
WHERE x.cre_surr_id = 9999999999999

Table structure is below.

CREATE TABLE ALTERNATIVE_CREATION_IDS
(
  ALTERNATIVE_CREATION_ID     VARCHAR2(100 BYTE) NOT NULL,
  CRE_SURR_ID                 NUMBER(12)        NOT NULL,
  TP_SURR_ID                  NUMBER(12),
  GC_ALT_USAGE_ID_TYPE_TABLE  VARCHAR2(5 BYTE)  DEFAULT 'AUIT'                NOT NULL,
  GC_ALT_USAGE_ID_TYPE_CODE   VARCHAR2(5 BYTE)  NOT NULL,
  SOC_SURR_ID                 NUMBER(12),
  SURR_ID                     NUMBER(12)        NOT NULL)

and sample data is below.

--SQL Statement which produced this data:
--
--  select * from alternative_creation_ids where surr_id in (5068066723,
--  5068066724,
--  5068066725,
--  5068066726,
--  5068066727,
--  5068066729,
--  5068066731,
--  5068066732,
--  5068066733);
--
Insert into ALTERNATIVE_CREATION_IDS
   (ALTERNATIVE_CREATION_ID, CRE_SURR_ID, TP_SURR_ID, GC_ALT_USAGE_ID_TYPE_TABLE, GC_ALT_USAGE_ID_TYPE_CODE, 
    SOC_SURR_ID, SURR_ID)
 Values
   ('4YvUOe8q9N2kQ5aflrezTs', 4890969424, 9513, 'AUIT', 'PROD', 
    NULL, 5068066723);
Insert into ALTERNATIVE_CREATION_IDS
   (ALTERNATIVE_CREATION_ID, CRE_SURR_ID, TP_SURR_ID, GC_ALT_USAGE_ID_TYPE_TABLE, GC_ALT_USAGE_ID_TYPE_CODE, 
    SOC_SURR_ID, SURR_ID)
 Values
   ('5GgVV6DFbWofT50C0dMRl0', 4891029626, 9513, 'AUIT', 'PROD', 
    NULL, 5068066724);
Insert into ALTERNATIVE_CREATION_IDS
   (ALTERNATIVE_CREATION_ID, CRE_SURR_ID, TP_SURR_ID, GC_ALT_USAGE_ID_TYPE_TABLE, GC_ALT_USAGE_ID_TYPE_CODE, 
    SOC_SURR_ID, SURR_ID)
 Values
   ('4YyWUP4TOLM0lLKgbxXens', 4890969425, 9513, 'AUIT', 'PROD', 
    NULL, 5068066725);
Insert into ALTERNATIVE_CREATION_IDS
   (ALTERNATIVE_CREATION_ID, CRE_SURR_ID, TP_SURR_ID, GC_ALT_USAGE_ID_TYPE_TABLE, GC_ALT_USAGE_ID_TYPE_CODE, 
    SOC_SURR_ID, SURR_ID)
 Values
   ('5Gh2ldlMcEjcxGXQZO2erc', 4891029627, 9513, 'AUIT', 'PROD', 
    NULL, 5068066726);
Insert into ALTERNATIVE_CREATION_IDS
   (ALTERNATIVE_CREATION_ID, CRE_SURR_ID, TP_SURR_ID, GC_ALT_USAGE_ID_TYPE_TABLE, GC_ALT_USAGE_ID_TYPE_CODE, 
    SOC_SURR_ID, SURR_ID)
 Values
   ('5GhltgxuEfhzPiBxOoMKdl', 4891029628, 9513, 'AUIT', 'PROD', 
    NULL, 5068066727);
Insert into ALTERNATIVE_CREATION_IDS
   (ALTERNATIVE_CREATION_ID, CRE_SURR_ID, TP_SURR_ID, GC_ALT_USAGE_ID_TYPE_TABLE, GC_ALT_USAGE_ID_TYPE_CODE, 
    SOC_SURR_ID, SURR_ID)
 Values
   ('3JfOCUI9q8yb3lJdEJPPbE', 4890969284, 9513, 'AUIT', 'PROD', 
    NULL, 5068066729);
Insert into ALTERNATIVE_CREATION_IDS
   (ALTERNATIVE_CREATION_ID, CRE_SURR_ID, TP_SURR_ID, GC_ALT_USAGE_ID_TYPE_TABLE, GC_ALT_USAGE_ID_TYPE_CODE, 
    SOC_SURR_ID, SURR_ID)
 Values
   ('5GjwMbQWxB97ddPnqDAUt8', 4891029629, 9513, 'AUIT', 'PROD', 
    NULL, 5068066731);
Insert into ALTERNATIVE_CREATION_IDS
   (ALTERNATIVE_CREATION_ID, CRE_SURR_ID, TP_SURR_ID, GC_ALT_USAGE_ID_TYPE_TABLE, GC_ALT_USAGE_ID_TYPE_CODE, 
    SOC_SURR_ID, SURR_ID)
 Values
   ('5GjLSJ14WJlRSUe4MNoNLE', 4891029630, 9513, 'AUIT', 'PROD', 
    NULL, 5068066732);
Insert into ALTERNATIVE_CREATION_IDS
   (ALTERNATIVE_CREATION_ID, CRE_SURR_ID, TP_SURR_ID, GC_ALT_USAGE_ID_TYPE_TABLE, GC_ALT_USAGE_ID_TYPE_CODE, 
    SOC_SURR_ID, SURR_ID)
 Values
   ('5GjSKGaNJzhVlL9ygkyNbY', 4891029631, 9513, 'AUIT', 'PROD', 
    NULL, 5068066733);
COMMIT;

The correct Answer was by Alex Poole. Just for your information I went with the below in my XML SQL. I tried to get the tags to just show a blank value but for what ever reason it just showed the end tag (see below).

<ProdId>
   </Idtype>
   </id>
</ProdId>

So instead I went for 

<ProdId>
   <Idtype>N/A</Idtype>
   <id>N/A</id>
</ProdId>



 ,(       -- start level 5 tag prodId                                                        
   SELECT XMLELEMENT ("prodId",   
                        XMLELEMENT ("idType",x.idType),
                        XMLELEMENT ("idType",x.id))
   from
       (SELECT  DECODE (alternative_creation_ids.gc_alt_usage_id_type_code,'PROD',1,
                                      'ISAN',2,
                                      'CUI',3,9) AS oderby,
                DECODE (alternative_creation_ids.gc_alt_usage_id_type_code,'PROD','SOCIETY-ID',
                                       'ISAN','ISAN',
                                       'CUI','CLOCKID','N/A') AS idType,
               alternative_creation_ids.alternative_creation_id AS id,
               alternative_creation_ids.cre_surr_id AS cre_surr_id                                                
       FROM    alternative_creation_ids
       WHERE  alternative_creation_ids.gc_alt_usage_id_type_table =  'AUIT'
       AND alternative_creation_ids.gc_alt_usage_id_type_code in ('ISAN','CUI','PROD')
       order by 1) x                                                       
       WHERE x.cre_surr_id = cre.surr_id
       UNION ALL
       SELECT XMLELEMENT ("prodId",   
                  XMLELEMENT ("idType",'N/A'),
                  XMLELEMENT ("id",'N/A')
                         )
       FROM dual
       WHERE NOT EXISTS (SELECT null FROM alternative_creation_ids 
                         WHERE cre_surr_id = cre.surr_id)
   ) -- end level 5 tag prodId

Upvotes: 3

Views: 2247

Answers (1)

Alex Poole
Alex Poole

Reputation: 191560

If there is no data in the table then your existing query can't produce the empty tags. You can add a union to generate them, checking if the ID exists:

...
WHERE x.cre_surr_id = 9999999999999
UNION ALL
SELECT XMLELEMENT ("prodId",   
                  XMLELEMENT ("idType",null),
                  XMLELEMENT ("id",null)
                  )
FROM dual
WHERE NOT EXISTS (
  SELECT null FROM alternative_creation_ids WHERE cre_surr_id = 9999999999999
);

XMLELEMENT("PRODID",XMLELEMENT("IDTYPE",NVL(X.IDTYPE,'N/A')),XMLELEMENT("ID",NVL
--------------------------------------------------------------------------------
<prodId><idType></idType><id></id></prodId>

You could also do the union inside your inline view if you promote the filter into that (and move the order-by clause to the end; it doesn't make sense to have it inside the subquery):

SELECT XMLELEMENT ("prodId",   
                  XMLELEMENT ("idType",NVL(x.idType,'N/A')),
                  XMLELEMENT ("id",NVL(x.id,'N/A'))
                  )
FROM
    (SELECT  DECODE (alternative_creation_ids.gc_alt_usage_id_type_code,'PROD',1,
                                                'ISAN',2,
                                                'CUI',3,9) AS oderby,
             DECODE(alternative_creation_ids.gc_alt_usage_id_type_code,'PROD','SOCIETY-ID',
                                                                       'ISAN','ISAN',
                                                                        'CUI','CLOCKID','N/A') AS idType,
                                                                   alternative_creation_ids.alternative_creation_id AS id,
                                                                   alternative_creation_ids.cre_surr_id AS cre_surr_id                                                
    FROM    alternative_creation_ids
    WHERE  alternative_creation_ids.gc_alt_usage_id_type_table =  'AUIT'
    AND alternative_creation_ids.gc_alt_usage_id_type_code in ('ISAN','CUI','PROD')
    AND cre_surr_id = 9999999999999
    UNION ALL
    SELECT null, null, null, null
    FROM dual
    WHERE NOT EXISTS (
        SELECT null FROM alternative_creation_ids WHERE cre_surr_id = 9999999999999)
    ) x
ORDER BY oderBy
/

XMLELEMENT("PRODID",XMLELEMENT("IDTYPE",NVL(X.IDTYPE,'N/A')),XMLELEMENT("ID",NVL
--------------------------------------------------------------------------------
<prodId><idType>N/A</idType><id>N/A</id></prodId>

... but that picks up the NVL handling, which you don't seem to want.

Upvotes: 1

Related Questions