如何在命令式语言中实现MSSQL的OUTER APPLY XML解析功能？

核心逻辑对齐 #

MSSQL的OUTER APPLY本质是左集合的每个元素，关联右集合的所有匹配元素；若右集合无匹配，左元素仍保留，右字段用NULL填充。对应XML解析场景，就是：

• 父节点集合作为"左表"，每个父节点必须生成至少一条记录
• 子节点集合作为"右表"，每个子节点与父节点组合成一条记录；子节点为空时，父节点搭配默认值（NULL/空）生成记录

通用实现步骤 #

1. 定位父节点集合：用SelectNodes()获取所有需要作为左表的父节点（如订单节点）
2. 遍历父节点：对每个父节点执行以下操作：
   • 提取父节点的所有目标字段（属性/子标签），处理标签缺失的情况
   • 用SelectNodes()获取该父节点下的目标子节点集合（如订单明细）
   • 若子节点集合为空：生成一条父节点数据+子字段默认值的记录
   • 若子节点集合非空：遍历每个子节点，提取子节点字段，与父节点数据组合成记录
3. 异常防护：所有节点/属性访问前必须做存在性检查，避免空指针或程序崩溃

示例参考（含XML、MSSQL代码、多语言实现） #

示例XML

<Orders>
  <Order OrderID="1">
    <Customer>Alice</Customer>
    <Details>
      <Detail Product="Apple" Qty="2"/>
      <Detail Product="Banana" Qty="3"/>
    </Details>
  </Order>
  <Order OrderID="2">
    <Customer>Bob</Customer>
    <!-- 无Details节点 -->
  </Order>
  <Order OrderID="3">
    <!-- 无Customer标签，无Details节点 -->
  </Order>
</Orders>

对应MSSQL代码

SELECT 
  o.value('@OrderID', 'INT') AS OrderID,
  ISNULL(o.value('Customer[1]', 'NVARCHAR(50)'), '') AS Customer,
  ISNULL(d.value('@Product', 'NVARCHAR(50)'), '') AS Product,
  ISNULL(d.value('@Qty', 'INT'), 0) AS Qty
FROM 
  @xml.nodes('/Orders/Order') AS T(o)
OUTER APPLY 
  o.nodes('Details/Detail') AS D(d)

输出结果

VBA实现（使用MSXML） #

Sub ParseXML_OuterApply()
    Dim xmlDoc As MSXML2.DOMDocument60
    Dim parentNodes As MSXML2.IXMLDOMNodeList
    Dim parentNode As MSXML2.IXMLDOMNode
    Dim childNodes As MSXML2.IXMLDOMNodeList
    Dim childNode As MSXML2.IXMLDOMNode
    Dim orderID$, customer$, product$, qty$
    
    Set xmlDoc = New MSXML2.DOMDocument60
    xmlDoc.Load "C:\your_path\orders.xml"
    
    ' 获取父节点集合
    Set parentNodes = xmlDoc.SelectNodes("/Orders/Order")
    
    For Each parentNode In parentNodes
        ' 提取OrderID属性，强制检查存在性
        If parentNode.Attributes.getNamedItem("OrderID") Is Nothing Then
            orderID = "NULL"
        Else
            orderID = parentNode.Attributes.getNamedItem("OrderID").Text
        End If
        
        ' 提取Customer标签，处理缺失
        If parentNode.SelectSingleNode("Customer") Is Nothing Then
            customer = ""
        Else
            customer = parentNode.SelectSingleNode("Customer").Text
        End If
        
        ' 获取子节点集合（明细）
        Set childNodes = parentNode.SelectNodes("Details/Detail")
        
        ' 子节点为空时生成默认记录
        If childNodes.Length = 0 Then
            Debug.Print orderID & vbTab & customer & vbTab & "" & vbTab & "0"
        Else
            ' 遍历子节点生成关联记录
            For Each childNode In childNodes
                product = IIf(childNode.Attributes.getNamedItem("Product") Is Nothing, "", childNode.Attributes.getNamedItem("Product").Text)
                qty = IIf(childNode.Attributes.getNamedItem("Qty") Is Nothing, "0", childNode.Attributes.getNamedItem("Qty").Text)
                Debug.Print orderID & vbTab & customer & vbTab & product & vbTab & qty
            Next childNode
        End If
    Next parentNode
End Sub

Java实现（使用DOM解析） #

import org.w3c.dom.*;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import java.io.File;

public class XMLOuterApplyParser {
    public static void main(String[] args) {
        try {
            DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
            DocumentBuilder builder = factory.newDocumentBuilder();
            Document doc = builder.parse(new File("orders.xml"));
            doc.getDocumentElement().normalize();

            NodeList orderNodes = doc.getElementsByTagName("Order");
            for (int i = 0; i < orderNodes.getLength(); i++) {
                Element orderElem = (Element) orderNodes.item(i);
                
                // 提取OrderID，处理缺失
                String orderID = orderElem.hasAttribute("OrderID") ? orderElem.getAttribute("OrderID") : "NULL";
                // 提取Customer，处理缺失
                String customer = "";
                NodeList customerNodes = orderElem.getElementsByTagName("Customer");
                if (customerNodes.getLength() > 0) {
                    customer = customerNodes.item(0).getTextContent().trim();
                }
                
                // 获取明细节点
                NodeList detailNodes = orderElem.getElementsByTagName("Detail");
                if (detailNodes.getLength() == 0) {
                    System.out.printf("%s\t%s\t%s\t%s%n", orderID, customer, "", "0");
                } else {
                    for (int j = 0; j < detailNodes.getLength(); j++) {
                        Element detailElem = (Element) detailNodes.item(j);
                        String product = detailElem.hasAttribute("Product") ? detailElem.getAttribute("Product") : "";
                        String qty = detailElem.hasAttribute("Qty") ? detailElem.getAttribute("Qty") : "0";
                        System.out.printf("%s\t%s\t%s\t%s%n", orderID, customer, product, qty);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

C++实现（使用MSXML） #

#include <msxml6.h>
#include <iostream>
#include <string>
#pragma comment(lib, "msxml6.lib")

using namespace std;

string GetNodeText(IXMLDOMNode* node) {
    if (!node) return "";
    BSTR bstrText;
    node->get_text(&bstrText);
    string text = _bstr_t(bstrText);
    SysFreeString(bstrText);
    return text;
}

void ParseXMLWithOuterApply() {
    HRESULT hr = CoInitialize(NULL);
    if (FAILED(hr)) return;

    IXMLDOMDocument2* pDoc = NULL;
    CoCreateInstance(CLSID_DOMDocument60, NULL, CLSCTX_INPROC_SERVER, IID_IXMLDOMDocument2, (void**)&pDoc);
    pDoc->put_async(VARIANT_FALSE);
    pDoc->put_validateOnParse(VARIANT_FALSE);

    VARIANT_BOOL loadSuccess;
    pDoc->load(_variant_t(L"orders.xml"), &loadSuccess);
    if (!loadSuccess) {
        pDoc->Release();
        CoUninitialize();
        return;
    }

    IXMLDOMNodeList* pOrderNodes = NULL;
    pDoc->selectNodes(L"/Orders/Order", &pOrderNodes);
    if (!pOrderNodes) {
        pDoc->Release();
        CoUninitialize();
        return;
    }

    long orderCount;
    pOrderNodes->get_length(&orderCount);
    for (long i = 0; i < orderCount; i++) {
        IXMLDOMNode* pOrderNode = NULL;
        pOrderNodes->get_item(i, &pOrderNode);
        if (!pOrderNode) continue;

        // 提取OrderID
        IXMLDOMNamedNodeMap* pAttrs = NULL;
        pOrderNode->get_attributes(&pAttrs);
        IXMLDOMNode* pOrderIDNode = NULL;
        pAttrs->getNamedItem(L"OrderID", &pOrderIDNode);
        string orderID = GetNodeText(pOrderIDNode);
        if (orderID.empty()) orderID = "NULL";

        // 提取Customer
        IXMLDOMNode* pCustomerNode = NULL;
        pOrderNode->selectSingleNode(L"Customer", &pCustomerNode);
        string customer = GetNodeText(pCustomerNode);

        // 处理明细节点
        IXMLDOMNodeList* pDetailNodes = NULL;
        pOrderNode->selectNodes(L"Details/Detail", &pDetailNodes);
        if (!pDetailNodes || pDetailNodes->length == 0) {
            cout << orderID << "\t" << customer << "\t" << "" << "\t" << "0" << endl;
        } else {
            long detailCount;
            pDetailNodes->get_length(&detailCount);
            for (long j = 0; j < detailCount; j++) {
                IXMLDOMNode* pDetailNode = NULL;
                pDetailNodes->get_item(j, &pDetailNode);
                if (!pDetailNode) continue;

                IXMLDOMNamedNodeMap* pDetailAttrs = NULL;
                pDetailNode->get_attributes(&pDetailAttrs);
                IXMLDOMNode* pProductNode = NULL;
                pDetailAttrs->getNamedItem(L"Product", &pProductNode);
                string product = GetNodeText(pProductNode);

                IXMLDOMNode* pQtyNode = NULL;
                pDetailAttrs->getNamedItem(L"Qty", &pQtyNode);
                string qty = GetNodeText(pQtyNode);
                if (qty.empty()) qty = "0";

                cout << orderID << "\t" << customer << "\t" << product << "\t" << qty << endl;

                pProductNode->Release();
                pQtyNode->Release();
                pDetailAttrs->Release();
                pDetailNode->Release();
            }
        }

        pOrderIDNode->Release();
        pAttrs->Release();
        pCustomerNode->Release();
        pDetailNodes->Release();
        pOrderNode->Release();
    }

    pOrderNodes->Release();
    pDoc->Release();
    CoUninitialize();
}

int main() {
    ParseXMLWithOuterApply();
    return 0;
}

异常处理关键要点 #

• 节点/属性存在性检查：所有SelectSingleNode()、属性获取操作前，必须判断是否为NULL/空
• 统一默认值：标签/属性缺失时，统一返回空字符串、0或"NULL"，保证输出结构一致
• 递归适配嵌套结构：如果XML是多层嵌套（如订单→明细→子明细），可将遍历逻辑封装为递归函数，逐层处理父-子节点关联
• 资源释放：C++/Java中需注意释放DOM对象资源，避免内存泄漏

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