I am trying to extract the three printerLocations from this xml
the printerLocations are stored under printer/printer-header/elem with attribute name printerLocations

the question is how to get the results in a list like "USA,UK,(null)"
Or like in a column format that I can query like so:

select * from ( this xml query) where column1 = "USA"

this is a sample xml:

    <?xml version="1.0" encoding="UTF16" standalone="no" ?>
    <printer>
        <printer-header>
            <elem name="printerId">XROX101-19341</elem>
            <elem name="printerDate">05/11/19 12:27:48</elem>
            <elem name="printerLocations">
                <elem name="countryCd">USA</elem>
            </elem>
            <elem name="printerLocations">
                <elem name="countryCd">UK</elem>
            </elem>
            <elem name="printerLocations">
                <elem name="countryCd"/>
            </elem>
        </printer-header>
    </printer>

What I tried:

select  XMLTYPE(xml_string).extract('/printer/printer-header/elem[@name="printerLocations"]/elem[@name="countryCd"]/text()').getStringVal()   from printers 

/

This is getting just a sting value USA

The output i wish to get is a table with three columns,

COUNTRY1     COUNTRY2     COUNTRY3
USA          UK           NULL

CodePudding user response：

You can use XMLTable to extract multiple values, including parent element values, something like:

select printerId, printerDate, countryCd
from xmltable(
  '/printer/printer-header/elem[@name="printerLocations"]'
  passing xmltype('<?xml version="1.0" encoding="UTF16" standalone="no" ?>
<printer>
    <printer-header>
        <elem name="printerId">XROX101-19341</elem>
        <elem name="printerDate">05/11/19 12:27:48</elem>
        <elem name="printerLocations">
            <elem name="countryCd">USA</elem>
        </elem>
        <elem name="printerLocations">
            <elem name="countryCd">UK</elem>
        </elem>
        <elem name="printerLocations">
            <elem name="countryCd"/>
        </elem>
    </printer-header>
</printer>')
  columns
    printerId varchar2(30) path './../elem[@name="printerId"]',
    printerDate varchar2(17) path './../elem[@name="printerDate"]',
    countryCd varchar2(3) path 'elem[@name="countryCd"]'
)

You can convert the 'printDate' value to an actual date, assuming it's a fixed format.

Either way, you can then use that as an inline view or CTE.

db<>fiddle

What I tried: select XMLTYPE(xml_string).extract('/printer/printer-header/elem[@name="printerLocations"]/elem[@name="countryCd"]/text()').getStringVal() from printers

That will get all the country values as a single string; using XMLTable instead gets each value separately, as well as allowing you to easily get other data, and data for multiple XML documents at once.

As your string is coming from a table, that then becomes:

select x.printerId, x.printerDate, x.countryCd
from printers p
cross apply xmltable(
  '/printer/printer-header/elem[@name="printerLocations"]'
  passing xmltype(p.xml_string)
  columns
    printerId varchar2(30) path './../elem[@name="printerId"]',
    printerDate varchar2(17) path './../elem[@name="printerDate"]',
    countryCd varchar2(3) path 'elem[@name="countryCd"]'
) x

which is wrapped in an outer query, with converted date, as:

select * from (
  select
    printerId,
    to_date(printerDate, 'DD/MM/RR HH24:MI:SS') as printerDate,
    countryCd
  from printers p
  cross apply xmltable(
    '/printer/printer-header/elem[@name="printerLocations"]'
    passing xmltype(p.xml_string)
    columns
      printerId varchar2(30) path './../elem[@name="printerId"]',
      printerDate varchar2(17) path './../elem[@name="printerDate"]',
      countryCd varchar2(3) path 'elem[@name="countryCd"]'
  )
) x
where x.countryCd = 'USA'

db<>fiddle

All I need is a table with three columns

You could pivot that result; but more simply, you can use a similar method, targeting each printer location by index:

select x.countryCd1, x.countryCd2, x.countryCd3
from printers p
cross apply xmltable(
  '/printer/printer-header'
  passing xmltype(p.xml_string)
  columns
    countryCd1 varchar2(3) path 'elem[@name="printerLocations"][1]/elem[@name="countryCd"]',
    countryCd2 varchar2(3) path 'elem[@name="printerLocations"][2]/elem[@name="countryCd"]',
    countryCd3 varchar2(3) path 'elem[@name="printerLocations"][3]/elem[@name="countryCd"]'
) x

db<>fiddle

You could use three XMLQuery calls instead, but that would involve a bit more repetition.

Or if you really wanted to you could still use your original extract three times, with an index added:

select
  XMLTYPE(xml_string).extract('/printer/printer-header/elem[@name="printerLocations"][1]/elem[@name="countryCd"]/text()').getStringVal() as countryCd1,
  XMLTYPE(xml_string).extract('/printer/printer-header/elem[@name="printerLocations"][2]/elem[@name="countryCd"]/text()').getStringVal() as countryCd2,
  XMLTYPE(xml_string).extract('/printer/printer-header/elem[@name="printerLocations"][3]/elem[@name="countryCd"]/text()').getStringVal() as countryCd3
from printers

... which gets the same result.

Either way, you have to know in advance how many values (columns) you expect in the final result. (Otherwise you have to use dynamic SQL...)