We have stored xml data in a relational way in our database. Elements in hierarchical way, attributes in a seperate table, which can be joined to the several elements. We are able to generate the elements dynamically via dbms_xmlgen, but we found no solution for the xml attributes.
I have created an example, where the tables are simulated in two with-blocks: hirarchy for the elements and attr for the attributes
The two tables can be joined via "hirarchy.id = attr.element_id".
The followin statement gives us the XML with all elements, but without the attributes
SELECT DBMS_XMLGEN.getXML(
DBMS_XMLGEN.newcontextfromhierarchy(
'WITH hirarchy AS(' || chr(10) ||
' SELECT 1 ID, NULL parent_id, ''article_list'' NAME, NULL VALUE, 1 POS FROM dual UNION ALL' || chr(10) ||
' SELECT 2, 1, ''article'', ''shoe'', 2 FROM dual UNION ALL' || chr(10) ||
' SELECT 3, 1, ''article'', ''shirt'', 2 FROM dual' || chr(10) ||
'), attr AS(' || chr(10) ||
' SELECT 2 element_id, ''article_no'' NAME, ''123456789'' VALUE FROM dual UNION ALL' || chr(10) ||
' SELECT 3 element_id, ''article_no'' NAME, ''0815'' VALUE FROM dual UNION ALL' || chr(10) ||
' SELECT 3 element_id, ''name'' NAME, ''T-Shirt'' VALUE FROM dual' || chr(10) ||
')' || chr(10) ||
' SELECT LEVEL, XMLELEMENT(EVALNAME(h.name), h.value)' || chr(10) ||
' FROM hirarchy h' || chr(10) ||
'CONNECT BY PRIOR h.id = h.parent_id' || chr(10) ||
' START WITH h.parent_id IS NULL' || chr(10) ||
' ORDER SIBLINGS BY POS'
)
)
FROM dual
The result should look like this
<?xml version="1.0" encoding="UTF-8"?>
<article_list>
<article article_no="123456789">shoe</article>
<article article_no="0815" name="T-Shirt">shirt</article>
</article_list>
Now we are searching for a way to generate these attributes dynamically. Solution may use SQL or PL/SQL.
You can use XMLAttributes to define the attributes for an element. This takes a comma-separated list of names and values.
So you can get what you want by:
- Assigning a row number to the attributes for each element
- Outer joining the attributes to the hierarchy once for each position
- Passing the list of attribute name/values pairs to XMLAttributes
So you have N outer joins to the numbered attributes. This means you need to know the maximum number of attributes an element can have!
set long 10000
with rws as (
select q'!WITH hirarchy AS(
SELECT 1 ID, NULL parent_id, 'article_list' NAME, NULL VALUE, 1 POS FROM dual UNION ALL
SELECT 2, 1, 'article', 'shoe', 2 FROM dual UNION ALL
SELECT 3, 1, 'article', 'shirt', 2 FROM dual
), attr AS(
SELECT 2 element_id, 'article_no' NAME, '123456789' VALUE FROM dual UNION ALL
SELECT 3 element_id, 'article_no' NAME, '0815' VALUE FROM dual UNION ALL
SELECT 3 element_id, 'name' NAME, 'T-Shirt' VALUE FROM dual
), attr_rn as (
select a.*, row_number () over (
partition by element_id
order by name
) rn
from attr a
)
SELECT LEVEL,
XMLELEMENT(
EVALNAME(h.name),
xmlattributes (
a1.value as EVALNAME(a1.name) ,
a2.value as EVALNAME(a2.name)
),
h.value
) x
FROM hirarchy h
left join attr_rn a1
on a1.element_id = h.id
and a1.rn = 1
left join attr_rn a2
on a2.element_id = h.id
and a2.rn = 2
CONNECT BY PRIOR h.id = h.parent_id
START WITH h.parent_id IS NULL
ORDER SIBLINGS BY POS
!' x from dual
)
SELECT
DBMS_XMLGEN.getXML(
DBMS_XMLGEN.newcontextfromhierarchy(
x
)
) ,
x
FROM rws
/
DBMS_XMLGEN.GETXML(DBMS_XMLGEN.NEWCONTEXTFROMHIERARCHY(X)) X
<?xml version="1.0"?>
<article_list>
<article article_no="123456789">shoe</article>
<article article_no="0815" name="T-Shirt">shirt</article>
</article_list>
WITH hirarchy AS(
SELECT 1 ID, NULL parent_id, 'article_list' NAME, NULL VALUE, 1 POS FROM dual UNION ALL
SELECT 2, 1, 'article', 'shoe', 2 FROM dual UNION ALL
SELECT 3, 1, 'article', 'shirt', 2 FROM dual
), attr AS(
SELECT 2 element_id, 'article_no' NAME, '123456789' VALUE FROM dual UNION ALL
SELECT 3 element_id, 'article_no' NAME, '0815' VALUE FROM dual UNION ALL
SELECT 3 element_id, 'name' NAME, 'T-Shirt' VALUE FROM dual
), attr_rn as (
select a.*, row_number () over (
partition by element_id
order by name
) rn
from attr a
)
SELECT LEVEL,
XMLELEMENT(
EVALNAME(h.name),
xmlattributes (
a1.value as EVALNAME(a1.name) ,
a2.value as EVALNAME(a2.name)
),
h.value
) x
FROM hirarchy h
left join attr_rn a1
on a1.element_id = h.id
and a1.rn = 1
left join attr_rn a2
on a2.element_id = h.id
and a2.rn = 2
CONNECT BY PRIOR h.id = h.parent_id
START WITH h.parent_id IS NULL
ORDER SIBLINGS BY POSThere may be a more elegant way to do this, but if you want to generate the attribute names dynamically I'm not sure what it would be