Querying XML data in SQL

Question

I have an Oracle database that has an XML field in it. Let's say I have the following fields in the table "Party":

Date Time Guest Meal

And Meal is an XML field. When I query that field, I can see the following:

<table name="dinner">
    <row id="1">
        <field name="main">steak</field>
        <field name="side">potatoes</field>
        <field name="dessert">cake</field>
    </row>
</table>

How do I set up a SQL query so that it will output those individual XML field names and values as separate columns. So I want my output to be:

Date Time Guest Main Side Dessert

I have looked into XMLQUERY, but all the examples given involve node names like <Vendor></Vendor>, not <field name = "Vendor"></field>. I don't know how to translate those instructions.

While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your RDBMS and its version. All within the question as text, no images. — Yitzhak Khabinsky, Apr 14 '23 at 13:35
All paths in XMLTable are not node names, but XPaths. You need to provide XPath expressions for rows and columns — astentx, Apr 14 '23 at 15:00

Alex Poole · Answer 1 · 2023-04-14T13:44:38.303

You can use XMLTable to extract multiple values at once, something like:

select p.party_datetime, p.guest, x.main, x.side, x.dessert
from party p
cross apply xmltable(
  '/table/row'
  passing p.meal
  columns
    main varchar2(10) path 'field[@name="main"]',
    side varchar2(10) path 'field[@name="side"]',
    dessert varchar2(10) path 'field[@name="dessert"]'
) x

PARTY_DATETIME	GUEST	MAIN	SIDE	DESSERT
2023-05-01 19:00:00	Joe	steak	potatoes	cake

fiddle

The main XPath gets each row node, and the column clauses get the field values with matching attributes, using @name="..." to match.

For example the 'field[@name="main"]' path is looking for a node called field which an attribute called name, where that attribute has the value main, and gives the value of that node. Which is 'steak' in your example.

You said meal is "an XML field", so I've assumed the datatype of that column (not field) is XMLType. If it's actually a string (varchar2 or CLOB) then the passing clause would change to passing XMLType(p.meal) to convert it to that type.

(I've simplified slightly to a single date column, partly because you can't have a column called date unless it's a quoted identifier, but mostly because Oracle dates always have a time component so it doesn't usually make sense to try to store date and time separately. You can still format that single value as two columns in the result set, of course.)

You can read more in the documentation about XMLTable and querying XML data using SQL and PL/SQL.

Querying XML data in SQL

1 Answers1

Linked