This is a forum made up of volunteers who answer questions as their time permits.
Without setting up code to run your query, I can see that the XMLNamespaces clause is wrong. See
for an example of what a valid construct looks like.
Hi Thanks ,
I need some more information my XMl looks like below.
<?xml version="1.0" encoding="UTF-8"?>
In this i want the output like below,
StyleNumber LaRedouteNumber2 SKUNumber LaRedouteNumber
76624087 8575313 76624162 5952411
76624087 8575313 76624131 1828158
76624087 8575313 76624094 7311914.
As simple i want to get the column StyleNumber and LaRedouteNumber2 how many times the SKUNumber and LaRedouteNumber is there currently am using the below query.
select stylenumber, laredoutenumber, skunumber
from xml_laredoutenumbers xtab,
xmltable(XMLNamespaces ('http://www.cool.com/' as "a" ),
columns STYLENUMBER varchar2(25) path 'a:LaRedouteNumbers/StyleList/Style/StyleNumber'
, LAREDOUTENUMBER varchar2(25) path 'LaRedouteNumber'
, skunumber varchar2(10) path 'SKUNumber'
Please anybody helo me
With XMLTable, you cannot go up node levels (or see outside where the XPath picks), based on how XMLTable works internally. Just the way it is. In order to accomplish what you need, which is a classic parent/child relationship, you simply need to extract the child nodes for each parent and then process those child nodes in a separate XMLTable statement. Yes, you only have one parent in your example, but the concept still applies regardless of how many parents or children there are. What you are looking for is something like
select stylenumber, laredoutenumber2, x2.skunumber, x2.laredoutenumber from xml_laredoutenumbers xtab, xmltable(XMLNamespaces ('http://www.cool.com/' as "a" ), 'a:LaRedouteNumbers/StyleList/Style' passing xtab.xml_data columns STYLENUMBER varchar2(25) path 'StyleNumber' , LAREDOUTENUMBER2 varchar2(25) path 'LaRedouteNumber' , skuxml XMLType path 'Option_Style/Option/SKU_Option/SKU' ) x1, xmltable( '/SKU' passing x1.skuxml columns skunumber varchar2(25) path 'SKUNumber' , LAREDOUTENUMBER varchar2(25) path 'LaRedouteNumber' ) x2;
x1 is used to grab the parent and as part of that it also grabs the repeating child nodes (as skuxml). These child nodes are passed to x2, via the PASSING clause, and that maintains the parent/child relationship. It allows x2 to parse each child and then the rows are joined together to create the result set. You can include "skuxml" in your SELECT statement to see the XML that is passed from x1 to x2.