2 Replies Latest reply: Oct 8, 2012 10:23 AM by sdk11 RSS

    read XML message

    sdk11
      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 (11.2.0.3.0)

      Thank You
      --
        • 1. Re: read XML message
          BluShadow
          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>
            <REC>
              <COUNTRY>1</COUNTRY>
              <POINT>1800</POINT>
              <USER_INFO>
                <USER_ID>1</USER_ID>
                <TARGET>28</TARGET>
                <STATE>6</STATE>
                <TASK>12</TASK>
              </USER_INFO>
              <USER_INFO>
                <USER_ID>5</USER_ID>
                <TARGET>19</TARGET>
                <STATE>1</STATE>
                <TASK>90</TASK>
              </USER_INFO>
            </REC>
            <REC>
              <COUNTRY>2</COUNTRY>
              <POINT>2400</POINT>
              <USER_INFO>
                <USER_ID>3</USER_ID>
                <TARGET>14</TARGET>
                <STATE>7</STATE>
                <TASK>5</TASK>
              </USER_INFO>
            </REC>
          </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
              ,XMLTABLE('/RECSET/REC'
                        PASSING t.xml
                        COLUMNS country NUMBER PATH '/REC/COUNTRY'
                               ,point   NUMBER PATH '/REC/POINT'
                               ,user_info XMLTYPE PATH '/REC/*'
                       ) x
              ,XMLTABLE('/USER_INFO'
                        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
            sdk11
            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.