SQL提取XML格式CSV列的ITPCODE并关联多表查询

Hey there! Let's break this down step by step since you're new to SQL and dealing with XML data stored in a table column. The good news is most modern databases can handle XML parsing directly in SQL, so you might not need to export data and write loops (though we'll cover that fallback option too).

First, let's assume your existing inner join between PRT and PRTDESC looks something like this (I'm guessing the join key is PRTCODE—adjust if yours is different):

SELECT 
  p.PRTCODE,
  pd.PRTDescription
FROM PRT p
INNER JOIN PRTDESC pd ON p.PRTCODE = pd.PRTCODE

Now we need to join this with your purchase order table (let's call it PO) and extract the ITPCODE values from the XML in the CSV column, then link them to PRT.PRTCODE.

The exact syntax depends on which database you're using (SQL Server, MySQL, PostgreSQL, etc.). Here are examples for the most common ones:

SQL Server #

SQL Server has strong XQuery support to parse XML columns. Use CROSS APPLY to split each XML's <ITEM> nodes into individual rows, then join on ITPCODE:

SELECT 
  p.PRTCODE,
  pd.PRTDescription,
  po.PO_ID, -- Add any other PO fields you need
  item.value('(ITPCODE/text())[1]', 'VARCHAR(50)') AS ITPCODE
FROM PRT p
INNER JOIN PRTDESC pd ON p.PRTCODE = pd.PRTCODE
-- Join with PO table, then split XML into rows
INNER JOIN PO po
CROSS APPLY po.CSV.nodes('/FORMDATA/ITEM') AS Items(item)
-- Match the extracted ITPCODE to PRTCODE
WHERE p.PRTCODE = item.value('(ITPCODE/text())[1]', 'VARCHAR(50)')

• CROSS APPLY acts like a loop over each <ITEM> in the XML, turning them into separate rows.
• value() extracts the text inside <ITPCODE> for each row.

PostgreSQL #

PostgreSQL uses xmltable() to convert XML into relational rows:

SELECT 
  p.PRTCODE,
  pd.PRTDescription,
  po.PO_ID,
  x.itpcode
FROM PRT p
INNER JOIN PRTDESC pd ON p.PRTCODE = pd.PRTCODE
INNER JOIN PO po,
  xmltable('/FORMDATA/ITEM'
    PASSING po.CSV::XML -- Cast the column to XML type
    COLUMNS itpcode VARCHAR(50) PATH 'ITPCODE') x
WHERE p.PRTCODE = x.itpcode

• xmltable() defines how to map XML nodes to table columns (here, ITPCODE maps to the <ITPCODE> element).

MySQL #

MySQL's XML support is a bit more limited, but you can use a helper table of numbers to extract multiple <ITEM> nodes:

-- First create a temporary number table (adjust the number of rows for your max items per PO)
CREATE TEMPORARY TABLE nums (n INT);
INSERT INTO nums VALUES (1), (2), (3), (4), (5);

SELECT 
  p.PRTCODE,
  pd.PRTDescription,
  po.PO_ID,
  ExtractValue(po.CSV, CONCAT('/FORMDATA/ITEM[', nums.n, ']/ITPCODE')) AS ITPCODE
FROM PRT p
INNER JOIN PRTDESC pd ON p.PRTCODE = pd.PRTCODE
INNER JOIN PO po
INNER JOIN nums ON nums.n <= ExtractValue(po.CSV, 'count(/FORMDATA/ITEM)')
WHERE 
  p.PRTCODE = ExtractValue(po.CSV, CONCAT('/FORMDATA/ITEM[', nums.n, ']/ITPCODE'))
  AND ExtractValue(po.CSV, CONCAT('/FORMDATA/ITEM[', nums.n, ']/ITPCODE')) != '';

• The nums table lets us iterate over each <ITEM> position in the XML.
• ExtractValue() pulls the ITPCODE from the nth <ITEM> node.

If your database doesn't support XML parsing (e.g., very old versions or lightweight databases), you'll need to export the data and process it with a simple script. Here's a basic example using Python (no advanced coding skills required—just copy-paste and adjust file paths):

import csv
import xml.etree.ElementTree as ET

# Step 1: Export your PRT + PRTDESC data to a CSV (prt_data.csv) with columns PRTCODE, PRTDescription
# Step 2: Export your PO data to a CSV (po_data.csv) with columns PO_ID, CSV

# Load PRT data into a dictionary for quick lookup
prt_lookup = {}
with open('prt_data.csv', 'r') as prt_file:
    reader = csv.DictReader(prt_file)
    for row in reader:
        prt_lookup[row['PRTCODE']] = row['PRTDescription']

# Process PO data and match to PRT
with open('po_data.csv', 'r') as po_file, open('final_result.csv', 'w', newline='') as result_file:
    po_reader = csv.DictReader(po_file)
    result_writer = csv.writer(result_file)
    # Write header row
    result_writer.writerow(['PRTCODE', 'PRTDescription', 'PO_ID', 'ITPCODE'])
    
    for po_row in po_reader:
        xml_content = po_row['CSV']
        po_id = po_row['PO_ID']
        try:
            # Parse the XML string
            root = ET.fromstring(xml_content)
            # Loop through each <ITEM> in the XML
            for item in root.findall('.//ITEM'):
                itp_code = item.find('ITPCODE').text
                # Check if this ITPCODE exists in PRT
                if itp_code in prt_lookup:
                    result_writer.writerow([itp_code, prt_lookup[itp_code], po_id, itp_code])
        except Exception as e:
            print(f"Error processing PO {po_id}: {str(e)}")

• This script reads the exported CSVs, parses each XML string, extracts ITPCODEs, and matches them to your inventory data, saving the result to a new CSV.

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