oracle XMLTABLE conditional return

Question

I am trying to query a XMLType for conditional return.
My xml query :

with tab as (
select XMLType(
'<Obj name="Job" id="1">
    <Steps>
        <StepType2>
            <StepOptions>
                <Attribute name="attr1" value="val1"/>
                <Attribute name="attr2" value="val2"/>
                <Attribute name="attr3" value="val3"/>
            </StepOptions>
        </StepType2>
        <StepType1 type="flow" name="flowval" id="103"/>
        <StepType1 type="data" name="dataval" id="104"/>
        <StepType2>
            <StepOptions>
                <Attribute name="attr1" value="val5"/>
                <Attribute name="attr2" value="val6"/>
            </StepOptions>
        </StepType2>
    </Steps>
</Obj>') as xmldata
  from dual
)
select x.* 
  from tab,
       XMLTable( 'for $i in $XmlData//* 
                  return $i'
                  PASSING xmldata as "XmlData"
                  COLUMNS node VARCHAR2(2000) PATH 'name()',
                          name VARCHAR2(2000) PATH '@name',
                          id VARCHAR2(2000) PATH '@id'
                ) x

I do too many search on XML DB oracle, xPath and so on but I could not find any idea how to achieve my goal.

basically what I want to achieve this results :

• Display all Steps elements with their root parent, in this case root is 'Obj'
• if node name = 'StepType2' then
  for ATTR_NAME field get value if only name = 'attr1' and
  for ATTR_VAL field get value if only name = 'attr2'
• if node name = 'StepType1' then its much simple ATTR_NAME = @type, ATTR_VAL=@name and ATTR_ID=@id

I dont know even if it is possible but any idea to start would be great?

Answer 1

You can do this by cross joining the steptype xmls to the main xmls with a union all, like so:

with tab as (
select XMLType(
'<Obj name="Job" id="1">
    <Steps>
        <StepType2>
            <StepOptions>
                <Attribute name="attr1" value="val1"/>
                <Attribute name="attr2" value="val2"/>
                <Attribute name="attr3" value="val3"/>
            </StepOptions>
        </StepType2>
        <StepType1 type="flow" name="flowval" id="103"/>
        <StepType1 type="data" name="dataval" id="104"/>
        <StepType2>
            <StepOptions>
                <Attribute name="attr1" value="val5"/>
                <Attribute name="attr2" value="val6"/>
            </StepOptions>
        </StepType2>
    </Steps>
</Obj>') as xmldata
  from dual
),
  main_xml AS (SELECT mx.root_type,
                      mx.root_name,
                      mx.steptype1,
                      mx.steptype2
               FROM   tab
                      CROSS JOIN XMLTABLE('/*' PASSING tab.xmldata
                                          COLUMNS root_type VARCHAR2(50) PATH 'name(.)',
                                                  root_name VARCHAR2(50) PATH '@name',
                                                  steptype1 XMLTYPE PATH '//StepType1',
                                                  steptype2 XMLTYPE PATH '//StepType2') mx)
SELECT mx.root_type,
       mx.root_name,
       st1.attr_name,
       st1.attr_val,
       st1.attr_id
FROM   main_xml mx
       CROSS JOIN XMLTABLE('/StepType1' PASSING mx.steptype1
                           COLUMNS attr_name VARCHAR2(50) PATH '@type',
                                   attr_val VARCHAR2(30) PATH '@name',
                                   attr_id INTEGER PATH '@id') st1
UNION ALL
SELECT mx.root_type,
       mx.root_name,
       st2.attr_name,
       st2.attr_val,
       NULL attr_id
FROM   main_xml mx
       CROSS JOIN XMLTABLE('/StepType2' PASSING mx.steptype2
                           COLUMNS attr_name VARCHAR2(30) PATH '//Attribute[@name="attr1"]/@value',
                                   attr_val VARCHAR2(30) PATH '//Attribute[@name="attr2"]/@value',
                                   attr_id INTEGER PATH '@id') st2;

Which results in:

ROOT_TYPE ROOT_NAME ATTR_NAME ATTR_VAL    ATTR_ID
--------- --------- --------- -------- ----------
Obj       Job       flow      flowval         103
Obj       Job       data      dataval         104
Obj       Job       val1      val2     
Obj       Job       val5      val6