使用FOR XML PATH处理三张SQL表的技术实现问询

首先，先补全TEST_VENDOR表的插入语句（原语句不完整，这里添加合理的测试数据以便演示）：

INSERT INTO TEST_VENDOR (ID, PRODUCT_ID, VENDOR_NAME) 
VALUES (1,1,'Vendor A'), (2,1,'Vendor B'), (3,2,'Vendor C');

接下来，我们可以通过多表关联+嵌套子查询结合FOR XML PATH语法，生成层次化的XML结构，把产品、产品属性、供应商信息整合到一起：

SELECT
    p.PRODUCT_ID AS '@ProductId',
    p.PRODUCT_NAME AS 'ProductName',
    -- 嵌套生成产品属性节点
    (SELECT 
         pv.PROD_VALUES_VALUE AS 'Value'
     FROM TEST_PROD_VALUES pv
     WHERE pv.PRODUCT_ID = p.PRODUCT_ID
     FOR XML PATH('ProdValue'), TYPE),
    -- 嵌套生成供应商节点
    (SELECT 
         v.VENDOR_NAME AS 'Name'
     FROM TEST_VENDOR v
     WHERE v.PRODUCT_ID = p.PRODUCT_ID
     FOR XML PATH('Vendor'), TYPE)
FROM TEST_PRODUCT p
FOR XML PATH('Product'), ROOT('Products');

代码细节说明 #

• @ProductId：用@符号将PRODUCT_ID设置为XML节点的属性，而非普通子元素，让结构更符合XML属性规范
• TYPE关键字：确保子查询生成的XML作为节点嵌入主结果，避免被转义成纯文本字符串
• FOR XML PATH('Product')：将每条产品记录包装为<Product>节点
• ROOT('Products')：给整个XML文档添加根节点<Products>，让输出结构更完整

生成的XML输出示例 #

<Products>
  <Product ProductId="1">
    <ProductName>PRODUCT 1</ProductName>
    <ProdValue>
      <Value>PROD_VALUES 1</Value>
    </ProdValue>
    <Vendor>
      <Name>Vendor A</Name>
    </Vendor>
    <Vendor>
      <Name>Vendor B</Name>
    </Vendor>
  </Product>
  <Product ProductId="2">
    <ProductName>PRODUCT 2</ProductName>
    <ProdValue>
      <Value>PROD_VALUES 2</Value>
    </ProdValue>
    <Vendor>
      <Name>Vendor C</Name>
    </Vendor>
  </Product>
</Products>

如果需要更紧凑的结构（比如不需要单独的<ProdValue>或<Vendor>包装节点），可以调整子查询的PATH参数为空字符串：

SELECT
    p.PRODUCT_ID AS '@ProductId',
    p.PRODUCT_NAME AS 'ProductName',
    (SELECT pv.PROD_VALUES_VALUE FROM TEST_PROD_VALUES pv WHERE pv.PRODUCT_ID = p.PRODUCT_ID FOR XML PATH(''), TYPE) AS 'ProdValues',
    (SELECT v.VENDOR_NAME FROM TEST_VENDOR v WHERE v.PRODUCT_ID = p.PRODUCT_ID FOR XML PATH(''), TYPE) AS 'Vendors'
FROM TEST_PRODUCT p
FOR XML PATH('Product'), ROOT('Products');

这个版本会直接把属性值和供应商名称作为文本节点嵌入，适合不需要层级区分的场景。

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