Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

building XPath with the XML having the Namespace using PL SQL

Sandeep Sangameshwara-OracleNov 1 2013 — edited Nov 11 2013

While trying to build the path of each node, when the XML has no namespace in it , the below code works fine providing the result

1~/

2~/person/

3~/person/age/

4~/person/homecity/

5~/person/name/

6~/person/homecity/lat/

7~/person/homecity/name/

8~/person/homecity/long/

But when the xml is changed to

<person xmlns="urn:person" xmlns:lat="urn:lat">
<name>Rob</name>
<age>37</age>
<homecity>
    <name>London</name>
    <lat>123.000</lat>
    <long>0.00</long>
</homecity>
</person>"

The result on executing the below code is resulting into only into below result

1~/
 2~/person/

In the XML provided above, XML name space is not constant and it may vary for every XML. My requirement is to parse the complete XML, where i can read the XML with namespace and get the result as mentioned below

1~/
2~/person/
3~/person/age/
4~/person/homecity/
5~/person/name/
6~/person/homecity/lat:lat/
7~/person/homecity/name/
8~/person/homecity/long/

Can you please help me resolving the issue mentioned. Thanks in advance. --Code Snippet below :

DECLARE
  l_File VARCHAR2(32000) := '<person>
<name>Rob</name>
<age>37</age>
<homecity>
    <name>London</name>
    <lat>123.000</lat>
    <long>0.00</long>
</homecity>
</person>';
 
 l_Where_Clause VARCHAR2(100) := '/*';
 l_Append_Var   VARCHAR2(100) := '/';
 
 TYPE Ty_Paths IS TABLE OF VARCHAR2(1000) INDEX BY PLS_INTEGER;
 l_Ty_Paths      Ty_Paths;
 l_Ty_Paths_Temp Ty_Paths;
 
 TYPE Ty_Verifier IS TABLE OF VARCHAR2(1000) INDEX BY VARCHAR2(1000);
 l_Ty_Varifier Ty_Verifier;
 
l_Prev_Query_Rec VARCHAR2(100);
l_Index_Num      NUMBER := 0;
l_Cur_Exec_Row   NUMBER := 0;
BEGIN
 l_Ty_Paths(Nvl(l_Ty_Paths.COUNT, 0) + 1) := l_Append_Var;
 l_Cur_Exec_Row := 1;
 
 --Dbms_Output.put_line('Before entering the loop');
 
 LOOP
   l_Ty_Paths_Temp.DELETE;
  SELECT DISTINCT REPLACE(l_Append_Var || '/' || t.Xml || '/', '//', '/') BULK COLLECT
   INTO   l_Ty_Paths_Temp
  FROM   (SELECT Xmltype(Extract(VALUE(e), '/').Getstringval()) .Getrootelement() AS Xml
           FROM   TABLE(Xmlsequence(Extract(Xmltype(l_File), l_Where_Clause))) e) t;
 
  l_Ty_Varifier(Nvl(l_Ty_Varifier.COUNT, 0) + 1) := l_Append_Var;
  --Dbms_Output.put_line('L_TY_PATHS_TEMP.Count::'||L_TY_PATHS_TEMP.Count);
  IF l_Ty_Paths_Temp.COUNT > 0 THEN
     l_Index_Num := Nvl(l_Ty_Paths.COUNT, 0) + 1;
     FOR i IN l_Ty_Paths_Temp.FIRST .. l_Ty_Paths_Temp.LAST LOOP
        l_Ty_Paths(l_Index_Num) := l_Ty_Paths_Temp(i);
        --Dbms_Output.put_line('L_INDEX_NUM::'||L_INDEX_NUM);
        --Dbms_Output.put_line('L_TY_PATHS(L_INDEX_NUM)::'||L_TY_PATHS(L_INDEX_NUM));
        l_Index_Num := l_Index_Num + 1;
     END LOOP;
  END IF;
  --Dbms_Output.put_line('L_TY_PATHS.Count::'||L_TY_PATHS.Count);
  --Dbms_Output.put_line('L_TY_PATHS.Count::'||L_CUR_EXEC_ROW);
 
  IF (NOT l_Ty_Paths.EXISTS(l_Cur_Exec_Row + 1)) OR (l_Cur_Exec_Row = l_Ty_Paths.COUNT) THEN
     --Dbms_Output.put_line('Exiting');
     EXIT;
  ELSE
     --Dbms_Output.put_line('Inside the Else part');
 
     l_Cur_Exec_Row := l_Cur_Exec_Row + 1;
     l_Append_Var   := l_Ty_Paths(l_Cur_Exec_Row);
     l_Where_Clause := l_Ty_Paths(l_Cur_Exec_Row) || '*';
  END IF;
 
  --To Display the record:
     --Dbms_Output.put_line(L_TY_PATHS.Count);
  END LOOP;
  IF l_Ty_Paths.COUNT > 0 THEN
    FOR i IN l_Ty_Paths.FIRST .. l_Ty_Paths.LAST LOOP
      Dbms_Output.Put_Line(i || ' record is ' || l_Ty_Paths(i));
   END LOOP;
 END IF;
 
END;
This post has been answered by odie_63 on Nov 11 2013
Jump to Answer

Comments

843807
Depending on experience I suggest writting it by hand. This will take time but you should get familiar with swing. Having said that. Before you go out to buy JBuider ( expensive ), I suggest you go and download netbeans. Its a free IDE with GUI builder. This is a Sun based product and again its free. www.netbeans.org.

With this tool as with most java gui builders you can drop your own beans in. Both visible and invisible. So for example you have: MyTextbox extends JTextField you can add this to your component pallete. . meaning that you are able to drag and drop your own components into the frame.

There are some rules that you should adhear to. . When editting the code in netbeans the necessary generated code is non-editable. You can edit it in another editor, but it may not load and display properly the next time you look at the class in the GUI editor../ . Imagine this to be the same with most editors. Anywho, I've said to much already. . .have fun with this one
843807
Thank you for the information!,

So it sounds like you strongly recommend net beans over Forte For Java?
Is this true? I like Forte For Java but it seems to be slow.
843807
Interestingly, Forte for Java is based on the NetBeans IDE !!
Therefore, if you are of the idea to use NetBeans, you may as well continue using Forte itself..

Try initially to code by hand. That will help you build a strong foundation. Then u can go ahead with using a visual builder. As far as I am concerned, Forte is probably the best IDE for Java, If u r planning to buy, then go in for IBM Visual Age. It is equally good.

Also, if u r still bent on using JBuilder, u can download a free personal edition..

cheers
Ramanujam
1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 9 2013
Added on Nov 1 2013
16 comments
2,265 views