如何在DB2中实现类似SQL Server的XML字段提取至独立列？

Got it, let's figure out how to replicate that SQL Server XML query behavior in DB2. The goal is to pull those XML fields into separate relational columns, right? DB2 uses XMLTABLE as its primary tool for turning XML data into row-and-column format, which replaces the nodes() + CROSS APPLY approach from SQL Server.

Here's the equivalent DB2 query #

Assuming your XML data is stored in a table (let's call it your_table) with an XML-type column named xml_value, this query will get you the same output:

SELECT 
    x.doc_id,
    x.reci_code
FROM 
    your_table t,
    XMLTABLE(
        '/docs' PASSING t.xml_value
        COLUMNS 
            doc_id VARCHAR(8) PATH 'doc_id',
            reci_code CHAR(8) PATH 'reci/reci_code'
    ) AS x;

Breaking down what this does: #

• XMLTABLE takes your XML data (via the PASSING clause) and maps elements to columns using XPath expressions.
• The PATH values directly target the elements from your XML structure:
  • doc_id pulls the value from the /docs/doc_id element
  • reci_code pulls from /docs/reci/reci_code
• We match the data types exactly to your original SQL Server query (VARCHAR(8) for doc_id, CHAR(8) for reci_code) to keep consistency.

If you're working with a literal XML string instead of a table column #

You can parse the XML directly in the query like this:

SELECT 
    x.doc_id,
    x.reci_code
FROM 
    XMLTABLE(
        '/docs' PASSING XMLPARSE(DOCUMENT '<docs><doc_id>1234</doc_id> <reci><reci_code>ss</reci_code> </reci></docs>')
        COLUMNS 
            doc_id VARCHAR(8) PATH 'doc_id',
            reci_code CHAR(8) PATH 'reci/reci_code'
    ) AS x;

Running either of these will give you the output you expect:

内容的提问来源于stack exchange，提问作者Saravanan Svn