Read XML document with PL/SQL, XML tag are not fixed

Question

Need to read XML document and get the values of element tag and their values. but until run-time the values of the XML element nodes are unknown. so can't use XPATH to extract values. Any suggestions to handle this problem

assume this is the xml that i have. all the elements names are unknown. Don't know how many is there. what i want to get element name, element level, value. this xml contain template for another xml document.

<d2>
      <d3>
         <d4>
            <d5>
               <d6>@name@</d6>
               <d6>@name@</d6>
            </d5>
            <d5>
               <d6>@name@</d6>
               <d6>@name@</d6>
            </d5>
            <d5>@name@</d5>
            <d5>@name@</d5>
         </d4>
      </d3>
      <d3>@name@</d3>
      <d3>
         <d4>@name@</d4>
         <d4>
            <d5>@age@</d5>
            <d5>
               <d6>@name@</d6>
               <d6>@date@</d6>
            </d5>
         </d4>
      </d3>
</d2>

values are unknown, or entire chierarchy is unknown ? You should upload some code... — Bartosz Olchowik, Oct 11 '19 at 09:29

score 0 · Answer 1 · answered Oct 11 '19 at 10:52

Little explanation what is going on.

Xml examples are copied form https://www.w3schools.com/xml/.
'//*[text()]' - selection all text nodes form xml
for ordinality - generate row num for xml
name(.) or local-name(.) -returning node name with or without namespace prefix
string-join(ancestor-or-self::*/name(.),"/") - path to the text node ( all parents)
./text() - value of node

.

with  test as (select xmltype('<breakfast_menu>
<food>
        <name>Belgian Waffles</name>
        <price>$5.95</price>
        <description>
       Two of our famous Belgian Waffles with plenty of real maple syrup
       </description>
        <calories>650</calories>
</food>
<food>
        <name>Strawberry Belgian Waffles</name>
        <price>$7.95</price>
        <description>
        Light Belgian waffles covered with strawberries and whipped cream
        </description>
        <calories>900</calories>
</food>
</breakfast_menu>') xx from dual
       union all 
 select xmltype('<root xmlns:h="http://www.w3.org/TR/html4/"
    xmlns:f="https://www.w3schools.com/furniture">
    <h:table>
      <h:tr>
        <h:td>Apples</h:td>
        <h:td>Bananas</h:td>
      </h:tr>
    </h:table>
    <f:table>
      <f:name>African Coffee Table</f:name>
  <f:width>80</f:width>
  <f:length>120</f:length>
</f:table>
    </root>'
) xx from dual
    )  
select r.*
              from test
                   , xmltable('//*[text()]' passing xx
                              columns xp_lp        for ordinality                
                           , xp_node varchar(4000) path 'name(.)'                      
                           , xp_node1 varchar(4000) path 'local-name(.)'
                           , xp_parent varchar(4000) path 'string-join(ancestor-or-self::*/name(.),"/")'
                           , xp_value varchar(4000) path './text()'
                           , xp_namespaces varchar2(4000) path 'namespace-uri(.)') r

score 0 · Answer 2 · answered Oct 16 '19 at 04:36

0

We could use DBMS_XMLDOM built-in package methods, [DBMS_XMLDOM]:https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_xmldom.htm#i1076719

answered Oct 16 '19 at 04:36

Osura Weerasinghe

73
7

Read XML document with PL/SQL, XML tag are not fixed

2 Answers2