Shaun Kinnair Shaun Kinnair - 1 month ago 6
SQL Question

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;

Answer

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.