1 2 3 Previous Next 35 Replies Latest reply on Feb 12, 2019 3:46 PM by Mike Kutz Go to original post
      • 15. Re: how to extract  of xmltype data
        heloo

        it is because each record has different node with attribute. I never know that the max. no. of a3 some record may last for <a3 m="100"> and some record may have only last for <a3 m="50"> and some record will have only 1 a3 node <a3>xxx</a3>

         

        how can i get the max no. of node for each record.

        • 16. Re: how to extract  of xmltype data
          Gaz in Oz

          As XML string or as values?

          You need to supply proper sample data AND exactly what the output is you are expecting for that sample data.

          • 17. Re: how to extract  of xmltype data
            heloo

            it means i can't use the following script as it is hardcord to retrieve the value of each node and its attribute. as each record will have different node with different no. of attribute, i never know the max number of attribue m=?

             

            1. select x.* 
            2. from your_xml t, 
            3. 3       xmltable('/row' 
            4. 4                passing xmltypecolumn 
            5. 5                columns id    NUMBER      PATH '@id'
            6. 6                        a1    VARCHAR2(9) PATH 'a1'
            7. 7                        a2    VARCHAR2(9) PATH 'a2'
            8. 8                        a3_1  VARCHAR2(9) PATH 'a3[1]'
            9. 9                        a3_1m VARCHAR2(5) PATH 'a3[1]/@m'
            10. 10                        a3_2  VARCHAR2(9) PATH 'a3[2]'
            11. 11                        a3_2m VARCHAR2(5) PATH 'a3[2]/@m'
            12. 12                        a3_3  VARCHAR2(9) PATH 'a3[3]' 
            13. 13*      ) x 
            • 18. Re: how to extract  of xmltype data
              Gaz in Oz
              SQL> ed
              Wrote file afiedt.buf
              
                1  select t.id, x.*
                2  from your_xml t,
                3       xmltable('/row'
                4                passing xmltypecolumn
                5                columns id NUMBER      PATH '@id',
                6                        a1   VARCHAR2(9) PATH 'a1',
                7                        a2   VARCHAR2(9) PATH 'a2',
                8                        a3_1 VARCHAR2(9) PATH 'a3[1]',
                9                        a3_m XMLTYPE     PATH 'a3[@m]'
               10       ) x
               11* order by 1
              SQL> /
              
                      ID         ID A1        A2        A3_1      A3_M
              ---------- ---------- --------- --------- --------- --------------------------------------------------
                       1          1 name      address1  tel1      <a3 m="2">20181231</a3>
                                                                  <a3 m="3">date3</a3>
                       2          1 name2     address2  tel2      <a3 m="3">date2</a3>
                       3          1 name3     address3  tel3      <a3 m="2">20181231</a3>
                                                                  <a3 m="3">date4</a3>
              
              3 rows selected.
              
              SQL>
              
              • 19. Re: how to extract  of xmltype data
                heloo

                you mean a3_m XMLTYPE     PATH 'a3[@m]' will return m>2 value?

                but how can i know which node will have attribute m>=2?

                • 20. Re: how to extract  of xmltype data
                  Gaz in Oz

                  you mean a3_m XMLTYPE     PATH 'a3[@m]' will return m>2 value?

                  No. a3[@m] will return all. It just so happens in your sample data from elsewhere, m is always >= 2. (2, 3).

                  but how can i know which node will have attribute m>=2?

                  By checking using Xquery.

                  Here's one way to explicitly check that there is an "m >= 2" using xmlexists():

                  SQL> ed
                  Wrote file afiedt.buf
                  
                    1  select t.id, x.*
                    2  from your_xml t,
                    3       xmltable('/row'
                    4                passing xmltypecolumn
                    5                columns id NUMBER      PATH '@id',
                    6                        a1   VARCHAR2(9) PATH 'a1',
                    7                        a2   VARCHAR2(9) PATH 'a2',
                    8                        a3_1 VARCHAR2(9) PATH 'a3[1]',
                    9                        a3_m XMLTYPE     PATH 'a3[@m]'
                   10       ) x
                   11  where  xmlexists('/row/a3[@m >= 2]'
                   12                   passing xmltypecolumn
                   13         )
                   14* order by 1
                  SQL> /
                  
                          ID         ID A1        A2        A3_1      A3_M
                  ---------- ---------- --------- --------- --------- ----------------------------------
                           1          1 name      address1  tel1      <a3 m="2">20181231</a3>
                                                                      <a3 m="3">date3</a3>
                           2          1 name2     address2  tel2      <a3 m="3">date2</a3>
                           3          1 name3     address3  tel3      <a3 m="2">20181231</a3>
                                                                      <a3 m="3">date4</a3>
                  
                  3 rows selected.
                  
                  SQL>
                  
                  • 21. Re: how to extract  of xmltype data
                    heloo

                    select t.id,x.* 

                    from table_1 t

                    xmltable('/row' passing xmlcolumn

                    columns id   NUMBER      PATH '@id'

                      a3_m XMLTYPE     PATH 'a3[@m]' 

                    ) x 

                    order by 1

                     

                    -------------

                    error:ExampleExceptionFormatter: exception message was: ORA-00933: SQL command not properly ended

                    • 22. Re: how to extract  of xmltype data
                      Gaz in Oz

                      select t.id,x.*

                      from table_1 t

                      xmltable('/row' passing xmlcolumn

                      columns id  NUMBER      PATH '@id',

                        a3_m XMLTYPE    PATH 'a3[@m]'

                      ) x

                      order by 1

                       

                      -------------

                      error:ExampleExceptionFormatter: exception message was: ORA-00933: SQL command not properly ended

                      You are missing the trailing comma after "from table_1 t".

                      Get into the habit of formatting you code, it really helps when you need to debug it.

                      Here's your code formatted and with the missing comma added:

                      select t.id, x.*
                      from   table_1 t,
                             xmltable('/row'
                                      passing xmlcolumn
                                      columns id   NUMBER  PATH '@id',
                                              a3_m XMLTYPE PATH 'a3[@m]'
                             )       x
                      order by 1;
                      

                      Give that a go.

                      • 23. Re: how to extract  of xmltype data
                        heloo

                        how can i filter the record which xmlnode <a3 m="33"> not equal to 20181231?

                        • 24. Re: how to extract  of xmltype data
                          Paulzip

                          heloo wrote:

                           

                          how can i filter the record which xmlnode <a3 m="33"> not equal to 20181231?

                          You keep changing the requirements!  How can anyone score a goal if you keep moving the goal posts?  If we show you how to achieve this, you'll then change the question once again.

                           

                          XMLTable converts XML into relational table data, so you use it and XPath to extract the key aspects of data you want.

                           

                          So if you want to filter according to not equal to 20181231, extract is as part of the XMLTable and then apply a standard where clause based on it.

                          • 25. Re: how to extract  of xmltype data
                            John Thorton

                            Paulzip wrote:

                             

                            heloo wrote:

                             

                            how can i filter the record which xmlnode <a3 m="33"> not equal to 20181231?

                            You keep changing the requirements! How can anyone score a goal if you keep moving the goal posts? If we show you how to achieve this, you'll then change the question once again.

                             

                            Consider that one way to avoid OP changing question/requirements is to STOP responding & providing answers.

                            • 26. Re: how to extract  of xmltype data
                              Stefan Jager

                              By looking up the documentation and start figuring out for yourself how XQuery works. Once you've found the docs, create a few test datasets, use Oracle's tools to get answers from that data. Gradually add complexity to your test datasets until they match your actual data.

                               

                              Doing that would have taught you more, and cost you less time than constantly posting unclear questions here.

                              • 27. Re: how to extract  of xmltype data
                                Mike Kutz

                                Stefan Jager wrote:

                                 

                                By looking up the documentation and start figuring out for yourself how XQuery works. Once you've found the docs, create a few test datasets, use Oracle's tools to get answers from that data. Gradually add complexity to your test datasets until they match your actual data.

                                 

                                Doing that would have taught you more, and cost you less time than constantly posting unclear questions here.

                                I like to provide links when the recommendation is RTFM

                                Oracle XML Developer's Guide

                                 

                                As for "unclear question", this is where people like rp state:

                                Please post your entire Business Requirement.

                                 

                                Without that, I can't tell if a simple XQuery in XMLTable() is sufficient or something like Example 5-3 should be used.

                                 

                                (for OP) Additionally, you should have followed the FAQ per request.

                                John Thorton wrote:

                                ...

                                 

                                Please click on URL below & provide details as stated in #5 - #9 inclusive

                                 

                                How do I ask a question on the forums?

                                Answering 6 & 7 would have made some of the "this query doesn't work" posts unnecessary because we would have known that the data type was XMLType instead of CLOB.

                                 

                                 

                                Again - for the OP

                                 

                                WHAT IS YOUR ENTIRE BUSINESS REQUIREMENT?

                                • 28. Re: how to extract  of xmltype data
                                  heloo

                                  ok, i can open another question to discuss. thanks.

                                  • 29. Re: how to extract  of xmltype data
                                    heloo

                                    but it seem it is also related to how to retrieve the required record. not another topic.

                                     

                                    how can i filter the record which xmlnode <a3 m="33"> not equal to 20181231?

                                     

                                    SELECT * FROM table_a WHERE  XMLEXISTS('/row/a3[@m=''33'' and text() =20181231]' PASSING XMLRECORD)

                                     

                                    how can i select '/row/a3[@m=''33'' <>'20181231'