用列中的属性转换XML

Question

DECLARE @hDoc XML = 
'<orders>
    <mail>
        <set id="s" sequence="1" version="1.1" >
            <set id="order" sequence="4" version="">
                <property id="id" label="" sequence="0" resource_label="" show_type="" name="" ><![CDATA[&#8364; 10000]]></property>
                <set id="article" sequence="1" version="">
                    <property id="basepricevalue" label="" sequence="0" resource_label="" show_type="" name="" ><![CDATA[155.66]]></property>
                    <property id="quantity" label="" sequence="0" resource_label="" show_type="" name="" ><![CDATA[3]]></property>
                    <property id="senior" label="" sequence="0" resource_label="" show_type="" name="" ><![CDATA[16]]></property>                    
                    <property id="vat" label="" sequence="0" resource_label="" show_type="" name="" ><![CDATA[21]]></property>                    
                </set>
                <set id="article" sequence="2" version="">
                    <property id="basepricevalue" label="" sequence="0" resource_label="" show_type="" name="" ><![CDATA[465.99]]></property>
                    <property id="quantity" label="" sequence="0" resource_label="" show_type="" name="" ><![CDATA[12]]></property>
                    <property id="senior" label="" sequence="0" resource_label="" show_type="" name="" ><![CDATA[17]]></property>   
                    <property id="vat" label="" sequence="0" resource_label="" show_type="" name="" ><![CDATA[18]]></property>                                      
                </set>
            </set>
        </set>
    </mail>
</orders>';

所需的输出：

OrderID    basepricevalue     quantity     senior     vat
10000      155.66             3            16         21
10000      465.99             12           17         18

这是我目前拥有的，但是由于所有十字架的应用，性能真的很糟糕。我敢肯定，必须有更好的方法来做到这一点。

SELECT 
  orderid   = nd.x.value('(property/text())[1]', 'varchar(8000)'),
  price = ndprice.x.value('(text())[1]', 'varchar(100)'),
  quantity = ndquantity.x.value('(text())[1]', 'varchar(100)'),
  senior = ndsenior.x.value('(text())[1]', 'varchar(100)'),
  vat = ndvat.x.value('(text())[1]', 'varchar(100)')
FROM (VALUES (@hDoc)) hdoc(orders)
CROSS APPLY orders.nodes('orders/mail//set[@id="order"]') nd(x)
CROSS APPLY x.nodes('set[@id="article"]') nd2(x)
CROSS APPLY nd2.x.nodes('property[@id="basepricevalue"]') ndprice(x)
CROSS APPLY nd2.x.nodes('property[@id="quantity"]') ndquantity(x)
CROSS APPLY nd2.x.nodes('property[@id="senior"]') ndsenior(x)
CROSS APPLY nd2.x.nodes('property[@id="vat"]') ndvat(x)

Answer 1

您可以摆脱一些十字架应用，因为您可以使用query((获取某些元素

SELECT 
  orderid = O.orderset.query('property[@id="id"]/text()').value('.','varchar(max)'),
  price = A.article.query('property[@id="basepricevalue"]/text()').value('.','float'),
  quantity = A.article.query('property[@id="quantity"]/text()').value('.','int'),
  senior = A.article.query('property[@id="senior"]/text()').value('.','int'),
  vat = A.article.query('property[@id="vat"]/text()').value('.','int')
FROM (VALUES (@hDoc)) hdoc(orders)
CROSS APPLY orders.nodes('orders/mail//set[@id="order"]') O(orderset)
CROSS APPLY O.orderset.nodes('set[@id="article"]') A(article)

，或者您确实可以使用更具体的XPath直接使用Value((。
并首次发生[1]。

另外，您可以通过直接从XML类型中获取节点来摆脱额外的十字架。

SELECT 
  orderid = O.orderset.value('(property[@id="id"]/text())[1]','varchar(max)'),
  price = A.article.value('(property[@id="basepricevalue"]/text())[1]','float'),
  quantity = A.article.value('(property[@id="quantity"]/text())[1]','int'),
  senior = A.article.value('(property[@id="senior"]/text())[1]','int'),
  vat = A.article.value('(property[@id="vat"]/text())[1]','int')
FROM @hDoc.nodes('orders/mail//set[@id="order"]') O(orderset)
CROSS APPLY O.orderset.nodes('set[@id="article"]') A(article)