XMLTABLE is a powerful feature in Oracle databases that transforms XML data into relational format for easy querying. I would like to begin with comprehensive example.
In the example below, I’m using XMLTable to parse an XML document that represents worksheet data. XMLTable allows me to extract attributes (r, spans, x14ac:dyDescent) from <row> elements and values (r, v) from nested elements within each row. This approach efficiently converts XML data into structured SQL results.
with
q_xml as (
select xmltype('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac">
<sheetData>
<row r="1" spans="1:2" x14ac:dyDescent="0.25">
<c r="A1">
<v>1</v>
</c>
<c r="B1">
<v>5</v>
</c>
</row>
<row r="2" spans="1:2" x14ac:dyDescent="0.25">
<c r="A2">
<v>2</v>
</c>
<c r="B2">
<v>6</v>
</c>
</row>
<row r="3" spans="1:2" x14ac:dyDescent="0.25">
<c r="A3">
<v>3</v>
</c>
<c r="B3">
<v>7</v>
</c>
</row>
<row r="4" spans="1:2" x14ac:dyDescent="0.25">
<c r="A4">
<v>4</v>
</c>
<c r="B4">
<v>8</v>
</c>
</row>
</sheetData>
</worksheet>') c_xml from dual
)
select
c1_r,
c1_spans,
c1_dydescent,
c2_r,
c2_v
from q_xml,
xmltable(
xmlnamespaces(default 'http://schemas.openxmlformats.org/spreadsheetml/2006/main',
'http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac' as "x14ac"),
'worksheet/sheetData/row'
passing c_xml
columns
c1_r path '@r',
c1_spans path '@spans',
c1_dydescent path '@x14ac:dyDescent'
) t1,
xmltable(
xmlnamespaces(default 'http://schemas.openxmlformats.org/spreadsheetml/2006/main',
'http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac' as "x14ac"
),
'worksheet/sheetData/row/c'
passing c_xml
columns
c2_r path '@r',
c2_v path 'v'
) t2
where t1.c1_r = substr(t2.c2_r, -1);
In the other example, the XPath expression ‘root/rowset/row[@r = 3]’ is used to select a specific element within an XML document. It navigates through the hierarchy starting from the element, moving to the element, and then selecting the element that has an attribute r with the value 3. In the columns part, the @ symbol is used to extract attributes of the XML elements, such as @r to get the r attribute in row tag.
with
q_xml as (
select xmltype('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<root xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac">
<rowset>
<row r="1" name="FirstLine" x14ac:attr="2">
<c r="A1">
<v>1</v>
</c>
<c r="B1">
<v>5</v>
</c>
</row>
<row r="2" name="SecondLine" spans="1:25" x14ac:attr="4">
<c r="A2">
<v>2</v>
</c>
<c r="B2">
<v>6</v>
</c>
</row>
<row r="3" name="ThirdLine" spans="2:30" x14ac:attr="6">
<c r="A3">
<v>3</v>
</c>
<c r="B3">
<v>7</v>
</c>
</row>
<row r="4" name="FourthLine" spans="3:60" x14ac:attr="8">
<c r="A4">
<v>4</v>
</c>
<c r="B4">
<v>8</v>
</c>
</row>
</rowset>
</root>') c_xml from dual
)
select
--xmlserialize(document c_xmltype indent size = 2)
row_r,
row_name,
row_attr,
c_xmltype,
c_v
from q_xml,
xmltable(
xmlnamespaces(default 'http://schemas.openxmlformats.org/spreadsheetml/2006/main',
'http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac' as "x14ac"),
'root/rowset/row[@r = 3]'
passing c_xml
columns
row_r path '@r',
row_name path '@name',
row_attr path '@x14ac:attr',
c_xmltype xmltype path '/c',
c_v path '/v'
) t1;
with
q_xml as (
select xmltype('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<root xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac">
<rowset>
<row r="1" name="FirstLine" x14ac:attr="2">
<c r="A1">
<v>1</v>
</c>
<c r="B1">
<v>5</v>
</c>
</row>
<row r="2" name="SecondLine" spans="1:25" x14ac:attr="4">
<c r="A2">
<v>2</v>
</c>
<c r="B2">
<v>6</v>
</c>
</row>
<row r="3" name="ThirdLine" spans="2:30" x14ac:attr="6">
<c r="A3">
<v>3</v>
</c>
<c r="B3">
<v>7</v>
</c>
</row>
<row r="4" name="FourthLine" spans="3:60" x14ac:attr="8">
<c r="A4">
<v>4</v>
</c>
<c r="B4">
<v>8</v>
</c>
</row>
</rowset>
</root>') c_xml from dual
)
select
c_r,
c_v
from q_xml,
xmltable(
xmlnamespaces(default 'http://schemas.openxmlformats.org/spreadsheetml/2006/main',
'http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac' as "x14ac"),
'root/rowset/row/c[2]'
passing c_xml
columns
c_r path '@r',
c_v path '/v'
) t1;
I hope this helps 🙂