11 Replies Latest reply: May 1, 2013 10:25 PM by rp0428 RSS

    Select Statement Against XML File

    1006421
      I have the following XML file content that I need to select values from. I need two fields returned in my query: one field for Device name and one field for the corresponding Device GROUP name. I have been reading through all of the XMLTYPE and XMLTABLE literature but I cannot seem to get the syntax correct. Here is what I have so far (XML file is further below):

      The query I've tried:

      SELECT
      extract(p.xml_data, '/DeviceGroups/DeviceGroup/Devices/Device/@name') DEVICE,
      extract(p.xml_data, '/DeviceGroups/DeviceGroup/@name') DEVICEGROUP
      FROM (SELECT XMLTYPE(bfilename('GOSYNC', 'DeviceGroupConfiguration.xml'), nls_charset_id('UTF8')) xml_data FROM dual) p



      The XML file contents:

      <?xml version="1.0" ?>
      <DeviceGroups>
      <DeviceGroup name="MGCGas">
      <Devices>
           <Device name="2BTYA48535" />
      </Devices>
      </DeviceGroup>
      <DeviceGroup name="ARGas">
      <Devices>
           <Device name="C7JAAG000094" />
           <Device name="C7JAAG000095" />
           <Device name="C7JAAG000096" />
           <Device name="CND141HL62" />
      </Devices>
           </DeviceGroup>
      </DeviceGroups>
        • 1. Re: Select Statement Against XML File
          chris227
          with xml as (
          select
          xmltype(
          '<?xml version="1.0" ?>
          <DeviceGroups>
          <DeviceGroup name="MGCGas">
          <Devices>
          <Device name="2BTYA48535" />
          </Devices>
          </DeviceGroup>
          <DeviceGroup name="ARGas">
          <Devices>
          <Device name="C7JAAG000094" />
          <Device name="C7JAAG000095" />
          <Device name="C7JAAG000096" />
          <Device name="CND141HL62" />
          </Devices>
          </DeviceGroup>
          </DeviceGroups>') xml_t from dual
          )
          
          SELECT
           DeviceGroupName
          ,DeviceName
          FROM
           xml
          ,xmltable( '/DeviceGroups/DeviceGroup'
          passing
          xml_t
          columns
           DeviceGroupName varchar2(30) path '/DeviceGroup/@name'
          ,DeviceGroup1     XMLType path '/DeviceGroup/Devices'
          ) xml2
          ,xmltable( '/Devices/Device'
          passing
          xml2.DeviceGroup1
          columns
           DeviceName  varchar2(30) path '/Device/@name'
          )
          
          DEVICEGROUPNAME     DEVICENAME
          MGCGas     2BTYA48535
          ARGas     C7JAAG000094
          ARGas     C7JAAG000095
          ARGas     C7JAAG000096
          ARGas     CND141HL62
          Notice the "deep level" references like '/Device/@name'
          • 2. Re: Select Statement Against XML File
            1006421
            Thank you very much for your help. I can definitely see that you have the correct SQL, but when I run it I get the following from Oracle 11g R2:

            ORA-00600: internal error code, arguments: [kkoljt1], [], [], [], [], [], [], [], [], [], [], []

            I am guessing, after reading a number of forums, that this is related to the use of the WITH XML AS clause at the beginning. The 600 error is thought to be an Oracle bug. Is there any possible modification to the SQL or to my approach that would mitigate this error? Again, thank you very much.
            • 3. Re: Select Statement Against XML File
              rp0428
              That 'table expression' is just to create some data to deal with.

              You don't need that because you are getting your XMLTYPE column from your table.

              In other words, this is what that 'with' is trying to reproduce from your query
              SELECT XMLTYPE(bfilename('GOSYNC' . . .
              You just need the query itself, not the WITH part.

              We can't reproduce your exact query since you are using an xml file that you didn't provide.
              • 4. Re: Select Statement Against XML File
                1006421
                The contents of the XML file is in the original post at the beginning of this thread. Thanks very much.
                • 5. Re: Select Statement Against XML File
                  rp0428
                  So you just ignore the part of my comments that refer to work that YOU need to do to modify your query?

                  You got the code handed to you on a platter. They used the WITH clause to create the data. You already have the data. You don't need the WITH clause.

                  Please indicate which part of the above you do not understand.
                  • 6. Re: Select Statement Against XML File
                    1006421
                    I think you are assuming that I already have a separate, existing "table" containing the XML data from the WITH clause. I am actually trying to achieve all of this using a single query, so my hope was that the contents of the bfilename part of the query could somehow be used in conjunction with a subquery. That being said, I tried the approach of creating the separate table containing just the XML data and then I tried passing its field to the SQL that was handed to me on a platter. I received the same 600 error.

                    For the record, I'm very greatful for any help that people are willing to provide. I did not ignore anything you said. Please keep in mind that I am fairly new to these forums and to writing SQL for Oracle. Thanks for your patience.
                    • 7. Re: Select Statement Against XML File
                      rp0428
                      >
                      I think you are assuming that I already have a separate, existing "table" containing the XML data from the WITH clause.
                      >
                      No - I'm not. You have a file containing the xml and the code you got uses the WITH table expression to create the same xml and then queries that table expression AS IF if were a table.
                      >
                      I tried the approach of creating the separate table containing just the XML data and then I tried passing its field to the SQL that was handed to me on a platter. I received the same 600 error.
                      >
                      And when I do that on vanilla 11.2.0.1.0 it works just fine

                      First I use that 'platter' code with just a minor mod to create the table.
                      -- create the table with an XMLTYPE column holding the data
                      create table xml_test as
                      select
                      xmltype(
                      '<?xml version="1.0" ?>
                      <DeviceGroups>
                      <DeviceGroup name="MGCGas">
                      <Devices>
                      <Device name="2BTYA48535" />
                      </Devices>
                      </DeviceGroup>
                      <DeviceGroup name="ARGas">
                      <Devices>
                      <Device name="C7JAAG000094" />
                      <Device name="C7JAAG000095" />
                      <Device name="C7JAAG000096" />
                      <Device name="CND141HL62" />
                      </Devices>
                      </DeviceGroup>
                      </DeviceGroups>') xml_t from dual
                      Then I query the table itself instead of the table expression.
                      -- query the table 
                      SELECT
                       DeviceGroupName
                      ,DeviceName
                      FROM
                       xml_test
                      ,xmltable( '/DeviceGroups/DeviceGroup'
                      passing
                      xml_t
                      columns
                       DeviceGroupName varchar2(30) path '/DeviceGroup/@name'
                      ,DeviceGroup1     XMLType path '/DeviceGroup/Devices'
                      ) xml2
                      ,xmltable( '/Devices/Device'
                      passing
                      xml2.DeviceGroup1
                      columns
                       DeviceName  varchar2(30) path '/Device/@name'
                      )
                       
                      DEVICEGROUPNAME     DEVICENAME
                      MGCGas     2BTYA48535
                      ARGas     C7JAAG000094
                      ARGas     C7JAAG000095
                      ARGas     C7JAAG000096
                      ARGas     CND141HL62
                      No drips, no runs, no errors.

                      If you run the above and get an 0600 you should contact Oracle support.
                      • 8. Re: Select Statement Against XML File
                        1006421
                        Thank you very much. I think I have everything I need. I appreciate your help.
                        • 9. Re: Select Statement Against XML File
                          rp0428
                          Does that code work for you or are you getting an 0600?

                          This forum is to help everyone, not just you.

                          You need to post information so that others with a similar issue will know what works and what doesn't.
                          • 10. Re: Select Statement Against XML File
                            1006421
                            Yes, the code works for me. I ended up creating a view of the XML file itself, and then passing that view's field to the second query provided to me. No 600 error after that. I've said thank you numerous times to you and others who have helped... and I've now posted my results. I realize the forums are to help others, and this is my very first thread. It wouldn't kill you to say to be nice rather than abusive.
                            • 11. Re: Select Statement Against XML File
                              rp0428
                              I suggest you re-read the entire thread and all of my responses. There are NO abusive remarks in any of them.

                              But saying this
                              >
                              I think I have everything I need.
                              >
                              without posting what it is that finally resolved your issue is of little value to anyone with a similar problem.

                              The forums work best when EVERYONE contributes their knowledge and expertise.