Results 1 to 4 of 4

Thread: XML question

  1. #1
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467

    Unanswered: XML question

    9.2 DB

    I have a clob field that contains xml data. Is there no simple way extract all the occurances of certain values? For instance given:

    <actions>
    <action index="0" color="10"></action>
    <action index="1" color="35"></action>
    <action index="2" color="18"></action>
    </actions>

    stored in a CLOB field. I think there must be a simple way to get all the values for "color". All the examples I see around the net all talk about linking to external files etc. I am probably just dense here, but I appreciate your kind words.

    Todd
    NOTE: Please disregard the label "Senior Member".

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Here is an example that works in 10g and should work in 9iR2 since the DBMS_XMLDOM package already existed :

    Code:
    CREATE TABLE testClob
    (
        Id NUMBER,
        Text CLOB,
        CONSTRAINT testClob_Pk PRIMARY KEY (Id)
    );
    
    Insert into testClob values (1, '<actions><action index="0" color="10"></action><action index="1" color="35"></action><action index="2" color="18"></action></actions>');
    
    COMMIT
    Code:
    DECLARE
    
        dDoc DBMS_XMLDOM.DOMDocument;
        nlNodeList DBMS_XMLDOM.DOMNodeList;
        nNode DBMS_XMLDOM.DOMNode;
        nNode2 DBMS_XMLDOM.DOMNode;
        nmNodeMap DBMS_XMLDOM.DOMNamedNodeMap;
        aAttr DBMS_XMLDOM.DOMAttr;
        cText CLOB;
        i NUMBER;
        j NUMBER;
        strColor VARCHAR2(50);
        strIndex VARCHAR2(50);
        
    BEGIN
        
        -- Get the Clob from the table
        SELECT Text INTO cText
        FROM testClob
        WHERE Id = 1;
        
        -- Create the xml document from the Clob
        dDoc := DBMS_XMLDOM.NEWDOMDOCUMENT(cText);
        
        -- Get the nodes corresponding the the 'action' tag
        nlNodeList := DBMS_XMLDOM.GETELEMENTSBYTAGNAME(dDoc, 'action');
       
        -- Loop through the 'action' nodes 
        FOR i IN 0..DBMS_XMLDOM.GETLENGTH(nlNodeList)-1 LOOP
        
            -- Get the ith node
            nNode := DBMS_XMLDOM.ITEM(nlNodeList, i);
            
            -- Get the attributes of this node 
            nmNodeMap := DBMS_XMLDOM.GETATTRIBUTES(nNode);
            
            -- Find the 'index' attribute
            nNode2 := DBMS_XMLDOM.GETNAMEDITEM(nmNodeMap,'index');
            aAttr := DBMS_XMLDOM.MAKEATTR(nNode2);
            strIndex := DBMS_XMLDOM.GETVALUE(aAttr);
            DBMS_OUTPUT.PUT_LINE('Index = '||strIndex);
            
            -- Find the 'color' attribute
            nNode2 := DBMS_XMLDOM.GETNAMEDITEM(nmNodeMap,'color');
            aAttr := DBMS_XMLDOM.MAKEATTR(nNode2);
            strColor := DBMS_XMLDOM.GETVALUE(aAttr);
            DBMS_OUTPUT.PUT_LINE('Color = '||strColor);
            
        END LOOP;
        
        -- Free ressources
        DBMS_XMLDOM.FREENODE(nNode);
        DBMS_XMLDOM.FREENODE(nNode2);
        DBMS_XMLDOM.FREEDOCUMENT(dDoc);
        
    END;
    /
    And now in action :

    Code:
    rbaraer@Ora10g> CREATE TABLE testClob
    (
        Id NUMBER,
        Text CLOB,
        CONSTRAINT testClob_Pk PRIMARY KEY (Id)
    );  2    3    4    5    6
    
    Table created.
    
    rbaraer@Ora10g> Insert into testClob values (1, '<actions><action index="0" color="10"></action><action index="1" color="35"></action><action index="2" color="18"></action></actions>');
    
    1 row created.
    
    rbaraer@Ora10g> COMMIT;
    
    Commit complete.
    
    rbaraer@Ora10g> DECLARE
    
        dDoc DBMS_XMLDOM.DOMDocument;
      2    3    4      nlNodeList DBMS_XMLDOM.DOMNodeList;
      5      nNode DBMS_XMLDOM.DOMNode;
      6      nNode2 DBMS_XMLDOM.DOMNode;
      7      nmNodeMap DBMS_XMLDOM.DOMNamedNodeMap;
      8      aAttr DBMS_XMLDOM.DOMAttr;
      9      cText CLOB;
     10      i NUMBER;
     11      j NUMBER;
     12      strColor VARCHAR2(50);
        strIndex VARCHAR2(50);
     13   14
     15  BEGIN
     16
     17      SELECT Text INTO cText
     18      FROM testClob
     19      WHERE Id = 1;
     20
     21      -- Create the xml document from the Clob
        dDoc := DBMS_XMLDOM.NEWDOMDOCUMENT(cText);
     22   23
     24      -- Get the nodes corresponding the the 'action' tag
     25
     26      nlNodeList := DBMS_XMLDOM.GETELEMENTSBYTAGNAME(dDoc, 'action');
     27      -- Loop through the 'action' nodes
     28
     29      FOR i IN 0..DBMS_XMLDOM.GETLENGTH(nlNodeList)-1 LOOP
     30
     31          -- Get the ith node
     32          nNode := DBMS_XMLDOM.ITEM(nlNodeList, i);
     33
     34          -- Get the attributes of this node
     35          nmNodeMap := DBMS_XMLDOM.GETATTRIBUTES(nNode);
     36
     37          -- Find the 'index' attribute
     38          nNode2 := DBMS_XMLDOM.GETNAMEDITEM(nmNodeMap,'index');
     39          aAttr := DBMS_XMLDOM.MAKEATTR(nNode2);
     40          strIndex := DBMS_XMLDOM.GETVALUE(aAttr);
     41          DBMS_OUTPUT.PUT_LINE('Index = '||strIndex);
    
            -- Find the 'color' attribute
     42          nNode2 := DBMS_XMLDOM.GETNAMEDITEM(nmNodeMap,'color');
            aAttr := DBMS_XMLDOM.MAKEATTR(nNode2);
     43   44   45          strColor := DBMS_XMLDOM.GETVALUE(aAttr);
     46   47          DBMS_OUTPUT.PUT_LINE('Color = '||strColor);
     48
     49      END LOOP;
     50
     51      -- Free ressources
     52      DBMS_XMLDOM.FREENODE(nNode);
     53      DBMS_XMLDOM.FREENODE(nNode2);
     54      DBMS_XMLDOM.FREEDOCUMENT(dDoc);
     55
    END;
    /
     56   57  Index = 0
    Color = 10
    Index = 1
    Color = 35
    Index = 2
    Color = 18
    
    PL/SQL procedure successfully completed.
    
    rbaraer@Ora10g>
    HTH & Regards,

    rbaraer

    PS (Edit) : Here is the 9iR2 documentation for DBMS_XMLDOM.
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  3. #3
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    In addition :

    If you store the xml document in an XMLType column instead of a CLOB column, you'll be able to use the EXTRACT and EXTRACTVALUE functions and use xpath expressions.

    For example :

    Code:
    rbaraer@Ora10g> CREATE TABLE testXML
    (
        Id NUMBER,
        Text XMLTYPE,
        CONSTRAINT testXML_Pk PRIMARY KEY (Id)
    );  2    3    4    5    6
    
    Table created.
    
    rbaraer@Ora10g> Insert into testXML values (1, '<actions><action index="0" color="10"></action><action index="1" color="35"></action><action index="2" color="18"></action></actions>');
    
    1 row created.
    
    rbaraer@Ora10g> COMMIT;
    
    Commit complete.
    
    rbaraer@Ora10g> select extract(text, '/actions/action@color') from testXML;
    
    EXTRACT(TEXT,'/ACTIONS/ACTION@COLOR')
    -----------------------------------------------------------------------------------------------------------------------------------
    103518
    
    rbaraer@Ora10g>
    I'm not very familiar with xpath so maybe you can format that better than that :-).

    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    This can be done with one single statement:
    Code:
    SELECT extractvalue(value(p), '//@color')
    from theTable, 
           table(xmlsequence(extract(XMLType(xmldata), '/actions/action'))) p
    Edit: In case this is no self-evident xmldata is the CLOB column's name that contains the XML. theTable is the name of the table containing the xmldata column
    Last edited by shammat; 05-05-06 at 07:59.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •