如何在Oracle中从CLOB类型XML提取NAME节点值

Got it, let's work through this. You need to pull the text value from the <NAME> node inside the XML stored in your test_clob table's data_value CLOB column. The critical detail here is handling the default XML namespace in your XML content—skip this, and your queries won't find the nodes you're targeting.

Solution 1: Using XMLTable (Recommended, Modern Approach) #

This is the go-to method for Oracle 12c and later, as it’s flexible and avoids deprecated functions:

SELECT x.name_value
FROM test_clob,
     XMLTable(
       XMLNAMESPACES(default 'urn:dataworld-com:schemas:custom_navigator_node'),
       '/CustomNavigatorNode/NAME'
       PASSING XMLTYPE(data_value)
       COLUMNS name_value VARCHAR2(255) PATH '.'
     ) x;

Quick Breakdown:

• XMLTYPE(data_value): Converts the raw CLOB content into an XMLType object that Oracle can parse.
• XMLNAMESPACES(...): Declares the default namespace from your XML (xmlns="urn:dataworld-com:schemas:custom_navigator_node"), so the parser recognizes the node structure correctly.
• /CustomNavigatorNode/NAME: The XPath that targets the <NAME> node under the root <CustomNavigatorNode>.
• COLUMNS name_value ... PATH '.': Defines a column to hold the extracted text, where . refers to the text content of the current <NAME> node.

Solution 2: Using EXTRACTVALUE (Legacy Compatibility) #

If you’re working with an older Oracle version (pre-12c), you can use EXTRACTVALUE (note: this function is deprecated in newer releases):

SELECT EXTRACTVALUE(
         XMLTYPE(data_value),
         '/CustomNavigatorNode/NAME',
         'xmlns="urn:dataworld-com:schemas:custom_navigator_node"'
       ) AS name_value
FROM test_clob;

Quick Breakdown:

• The third parameter explicitly passes the namespace declaration to the function, ensuring it matches the XML’s structure so the node is found.

Expected Output #

Both queries will return exactly what you’re looking for:

NAME_VALUE
-------------------
Data report Value

Quick Notes #

• Ensure the XML in your CLOB is well-formed (no syntax errors), otherwise the XML conversion step will fail.
• If your table has multiple rows with XML content, these queries will return the <NAME> value for each row.

内容的提问来源于stack exchange，提问作者Mohd Shoeb