raushan2007
New Member
Can someone help me correct the XPath statement that goes into a SQL (Oracle) statement to extract the the price of the model from the XML below whose \[code\]<BrandName>\[/code\] is "Bravo" and whose model name is "XYZ789": \[code\]<CarDetails><Wheels> <Rims> <BrandName>Acme</BrandName> <Model> <Name>ABC123</Name> <Price>$350.00</Price> </Model> </Rims> <Rims> <BrandName>Bravo</BrandName> <Model> <Name>XYZ789</Name> <Price>$250.00</Price> </Model> <Model> <Name>GHI456</Name> <Price>$300.00</Price> </Model> </Rims></Wheels></CarDetails>\[/code\]The above XML Clob is stored in an Oracle table called \[code\]Cars\[/code\], which has columns titled 'id', 'version' and 'xml_document'.My SQL statement to extract the information that I want looks like this:\[code\]SELECT * FROM ( SELECT x.xml_document.GetClobVal() FROM Cars x WHERE extractValue(x.xml_document, '/a:CarDetails/b:Wheels/b:Rims[b:BrandName="Bravo"]/b:Model/b:Name', 'xmlns:a="cars:instance:3_1" xmlns:b="cars:conceptualcomponent:3_1"' ) in ('XYZ789') ORDER BY x.id, x.version) where ROWNUM <= 100\[/code\]... but it doesn't pull out any information at all. It should pull out the one record. I know for sure the data exists in the XML, and that this statement works when I change the XPath around inside the \[code\]extractValue\[/code\] call to get other values, so clearly the XPath in my SQL is wrong.\[code\]/a:CarDetails/b:Wheels/b:Rims[b:BrandName="Bravo"]/b:Model/b:Name\[/code\]Can anyone offer any suggestions as to what the XPath my SQL \[code\]extractValue\[/code\] command should look like?Thanks in advance.