使用SQL从XML中提取数据

Question

我有一个像下面这样的XML

Declare @BathData XML
SET @BathData='<Batch>
<Customers>
<Customer>
    <CustomerId>1</CustomerId>
    <Product>
        <ProductId>10</ProductId>
        <ProductId>11</ProductId>
    </Product>
</Customer>
<Customer>
        <CustomerId>2</CustomerId>
        <Product>
            <ProductId>22</ProductId>
            <ProductId>23</ProductId>
            <ProductId>25</ProductId>
        </Product>
</Customer>
 </Customers>
 </Batch>'

我想要的结果如下

CusomerId ProductId
1           10
1           11
2           20
2           23
2           35

，我用下面的方法来整理

 SELECT Finaldata.R.value('CustomerId[1]','int')            
 CustomerId,Finaldata.R.value('Product[1]','int') as ProductId
 FROM @BathData.nodes('//Batch/Customers/Customer') as Finaldata (R)

但显然它不起作用，快速响应真的很有帮助，谢谢

Answer 1

遍历productid。您可以从两层(../../CustomerId)获取客户:

SELECT  Finaldata.R.value('(../../CustomerId)[1]','int') as CustomerId
,       Finaldata.R.value('(.)[1]','int') as ProductId
FROM    @BathData.nodes('/Batch/Customers/Customer/Product/ProductId') 
             as Finaldata (R)

点.作为当前节点的引用。