This discussion is archived
11 Replies Latest reply: May 1, 2013 8:25 PM by rp0428 RSS

Select Statement Against XML File

1006421 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    Thank you very much. I think I have everything I need. I appreciate your help.
  • 9. Re: Select Statement Against XML File
    rp0428 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points