Querying Oracle table with XPath/extract returning empty string

Question

I'm trying to extract values from XML that is similar to below and is stored in an Oracle 10g table:

<?xml version="1.0" encoding="UTF-8"?>
<manifest xmlns="http://www.imsglobal.org/xsd/imscp_v1p1" ...">
    <organizations>
        <organization identifier="***"><item identifier="_745"><item identifier="_9700"><imsss:sequencing><imsss:scoreAndRollup isCompletionRolledUp="false" isMasteryRolledUp="false" isScoreRequired="true" scoreRollupWeight="1.0"/></organization>
    </organizations>
    <resources/>
</manifest>

The query I'm trying to use is:

select xmltype(t.xml).extract('/manifest/organizations/organization').getStringVal() from BLAxml t

The data type of the xml column in the underlying table is CLOB.

Unfortunately, every time I run this query it returns an empty string for the value rather than the contents of this portion of the XML statement. It doesn't seem to matter what portion of the XML I'm pointing to, it gives me an empty string.

Any help would be greatly appreciated. Thank you.

There are no text contents in the XML fragment you provided. What are you actually trying to extract? — helderdarocha
– helderdarocha, Commented Jun 11, 2014 at 2:40
I need to extract the various name value pairs for things like <item identifier='xxx'> and the boolean values like isCompletionRolledUp. I assumed that if used the xpath above that I would at least get the child nodes of 'organization'. Is that not the case? Thanks. — Trebor
– Trebor, Commented Jun 11, 2014 at 5:26
Yes. The expression you provided will select the organization node and all its children. But by converting it to a string value, the result is an empty string because the tags don't have any content (there are no text nodes in any of the descendants). You might want to extract that in XPath and manipulate the nodes after your retrieve them in another language, or select data in individual attributes. — helderdarocha
– helderdarocha, Commented Jun 11, 2014 at 5:59
Extract returns XmlType so you could perform additional extract() operations in the context of the result, and you can use getStringVal() to retrieve the string contents of attributes. — helderdarocha
– helderdarocha, Commented Jun 11, 2014 at 6:09
I'm not sure I can wrap my head around what you are suggesting since I get an empty string now. Can you please give me an example? Thank you. — Trebor
– Trebor, Commented Jun 11, 2014 at 15:22

helderdarocha · Accepted Answer · 2014-06-11 16:18:35Z

1

Your XML source has a default namespace:

xmlns="http://www.imsglobal.org/xsd/imscp_v1p1"

That means that all unprefixed elements in your file are bound to that namespace.

In XPath, elements must be prefixed in order to be part of a namespace. If a default namespace is provided, it depends on your host language to bind it to XPath. You always have two options:

Register the namespace (using the tools in your host language)
Ignore the namespace (using wildcards instead of element names in XPath)

1. Register the namespace

I am not sure about how you deal with this in PL/SQL, but according to the documentation you should probably be able to do something like this:

extract('/manifest/organizations/organization/item[2]/@identifier',  
        'xmlns="http://www.imsglobal.org/xsd/imscp_v1p1"')

2. Ignore the namespace

Without registering the namespace, you can still extract data if you match all elements and restrict them by matching their local name:

extract('/*[local-name()="manifest"]/*[local-name()="organizations"]/*[local-name()="organization"]/*[local-name()="item"][2]/@identifier')

answered Jun 11, 2014 at 16:18

helderdarocha

23.7k4 gold badges52 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Trebor Over a year ago

Thank you for all of the effort. Both "select xmltype(xml).extract('/*[local-name()="manifest"]/*[local-name()="organizations"]/*[local-name()="organization"]/*[local-name()="item"][2]/@identifier') from blaxml" and registering the namespace return null strings.

helderdarocha Over a year ago

You can try to debug this with a minimal XPath. Something like extract(count(/*)) which should return 1.

Collectives™ on Stack Overflow

Querying Oracle table with XPath/extract returning empty string

1 Answer 1

1. Register the namespace

2. Ignore the namespace

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1. Register the namespace

2. Ignore the namespace

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related