2 Replies Latest reply on Oct 8, 2012 3:23 PM by sdk11

    read XML message

      Hi All,

      I have an XML message with 40 elements. If I need to read this XML through PL/SQL procedure, then do I need to declare 40 variables to handle the values of the elemensts. Or is there a better way to do this. Please share your thoughts/Ideas.

      Oracle version: Oracle 11gR2 (

      Thank You
        • 1. Re: read XML message
          Not enough information. Please read {message:id=9360002} and post sufficient information for people to help you.

          An example of reading XML data...
          WITH t as (select XMLTYPE('
          </RECSET>') as xml from dual)
          -- END OF TEST DATA
          select x.country, x.point, y.user_id, y.target, y.state, y.task
          from t
                        PASSING t.xml
                        COLUMNS country NUMBER PATH '/REC/COUNTRY'
                               ,point   NUMBER PATH '/REC/POINT'
                               ,user_info XMLTYPE PATH '/REC/*'
                       ) x
                        PASSING x.user_info
                        COLUMNS user_id NUMBER PATH '/USER_INFO/USER_ID'
                               ,target  NUMBER PATH '/USER_INFO/TARGET'
                               ,state   NUMBER PATH '/USER_INFO/STATE'
                               ,task    NUMBER PATH '/USER_INFO/TASK'
                       ) y
             COUNTRY      POINT    USER_ID     TARGET      STATE       TASK
          ---------- ---------- ---------- ---------- ---------- ----------
                   1       1800          1         28          6         12
                   1       1800          5         19          1         90
                   2       2400          3         14          7          5
          • 2. Re: read XML message
            Thank You and sorry for not posting the XML. I thought it will be too big to read. I will certainly keep this in mind to post the needed information. Thanks a lot your Idea/example code really helped me.