使用XML Path Resultset添加sub标签到XML_VAR中

Question

我尝试使用一个唯一的查询来获取带有sub标签的@xml_var ..但是我从未为此国王的弥补。有人可以给我一些提示以获得正确的结果吗？意大利Thnaks Alen

来自

 DECLARE @xml_var XML  
    SET @xml_var =  (
                SELECT 
                    nRiga as LineNumber,
                    nRiga as BuyersOrderLineReference,
                    skuFornitore as SuppliersProductCode,
                    '' as BuyersProductCode,
                    FR.quantita as Amount
                FROM Testate FT
                JOIN Righe FR
                       ON (FT.idOrdine = FR.idOrdine AND FR.idStato <> 'A')
                --WHERE FT.idOrdine = @idOrdine
                WHERE ft.idOrdine = 'XXXXXXXXXXXXXX'
                Order by fr.nRiga
                FOR XML PATH('OrderLine')
                )
    SELECT @xml_var

我的结果就像...

<OrderLine>
<LineNumber>1</LineNumber>
<BuyersOrderLineReference>1</BuyersOrderLineReference>
<SuppliersProductCode>PTN-9050</SuppliersProductCode>
<BuyersProductCode />
<Amount>2</Amount>
</OrderLine>

我需要...

之类的东西

<OrderLine>
    <LineNumber Preserve="true">1</LineNumber>
    <OrderLineReferences>
        <BuyersOrderLineReference Preserve="true">1</BuyersOrderLineReference>
    </OrderLineReferences>
    <Product>
        <SuppliersProductCode>PTN-9050</SuppliersProductCode>
        <BuyersProductCode></BuyersProductCode>
    </Product>
    <Quantity>
        <Amount>2</Amount>
    </Quantity>
</OrderLine>

Answer 1

以下硬编码示例返回您似乎需要的XML：

，从桌子上取出值应该很容易。

情况可能会有所不同，如果有1:n -DATA，我假设1:1数据...

SELECT 'true' AS [LineNumber/@Preserve]
      ,1 AS LineNumber
      ,(
        --I assume this is 1:n
        SELECT 'true' AS [BuyersOrderLineReference/@Preserve]
              ,1 AS BuyersOrderLineReference
        FOR XML PATH('OrderLineReferences'),TYPE
       )   
       ,'PT-9050' AS [Product/SuppliersProductCode]
       ,'' AS [Product/BuyersProductCode]
       ,2 AS [Quantity/Amount]
FOR XML PATH('OrderLine');

结果

<OrderLine>
  <LineNumber Preserve="true">1</LineNumber>
  <OrderLineReferences>
    <BuyersOrderLineReference Preserve="true">1</BuyersOrderLineReference>
  </OrderLineReferences>
  <Product>
    <SuppliersProductCode>PT-9050</SuppliersProductCode>
    <BuyersProductCode></BuyersProductCode>
  </Product>
  <Quantity>
    <Amount>2</Amount>
  </Quantity>
</OrderLine>