sql - 如何查询父子表并以 XML 形式返回结果？

Question

我有两张表 Customer 和 Order

客户表有customerId、CustomerName

订单表有 OrderId、CustomerId、ProductId

我想要的结果是一个 xml 文件

<Customers>
  <Customer>
    <CustomerId>7</CustomerId>
    <CustomerName>def</CustomerName>
    <Orders>
      <Customerorder>
          <OrderId>5</OrderId>
          <CustomerId>1</CustomerId>
          <ProductId>3</ProductId>
      </Customerorder>
      <Customerorder>
          <OrderId>5</OrderId>
          <CustomerId>1</CustomerId>
          <ProductId>3</ProductId>
      </Customerorder>
    </Orders>
  </Customer>
  <Customer>
        <CustomerId>2</CustomerId>
        <CustomerName>abc</CustomerName>
        <Orders>
            <Customerorder>
                <OrderId>5</OrderId>
                <CustomerId>1</CustomerId>
                <ProductId>3</ProductId>
            </Customerorder>
            <Customerorder>
                <OrderId>5</OrderId>
                <CustomerId>1</CustomerId>
                <ProductId>3</ProductId>
            </Customerorder>
        </Orders>
    </Customer>
 </Customers>

标签： sql

Answer 1

您在 xml 中有错误数据，例如 id 1 和 id 7。

这是你需要的吗？

declare @Customer Table(CustomerID int, CustomerName varchar(50))
insert into @Customer(CustomerID, CustomerName)
Values
(1, 'def'), (2, 'abc')
select * from @Customer

declare @Order Table(OrderID int, CustomerID int, ProductID int)
insert into @Order(OrderID, CustomerID, ProductID)
Values
(5,1, 3), (6,1,4),
(4,2,4),(5,2,6)
select * from @Order


select 
      c.CustomerID
    , c.CustomerName
    , (select  
        (SELECT * FROM @Order o 
            WHERE o.CustomerID = c.CustomerID 
            FOR XML PATH('CustomerOrder'), type)
      FOR XML PATH ('Orders'), type)
From @Customer c
FOR XML PATH('Customer'), ROOT ('Customers')