时间戳

首页 > 解决方案 > sql server 上的 XML 解析

sql - sql server 上的 XML 解析

问题描述

如何以格式返回数据：

Column Name t01     t02     t03     t04
Data        c01     c02     c03     c04

<orders xmlns="www address">
    <order>
        <order-date>2019-09-05</order-date>
        <created-by>storefront</created-by>
        <original-order-no>000001</original-order-no>
        <currency>USD</currency>
        <taxation>gross</taxation>
        <invoice-no>0099999</invoice-no>
        <custom-attributes>
            <custom-attribute attribute-id="t01">c01</custom-attribute>
            <custom-attribute attribute-id="t02">c02</custom-attribute>
            <custom-attribute attribute-id="t03">c03</custom-attribute>
            <custom-attribute attribute-id="t04">c04</custom-attribute>
        </custom-attributes>    
    </order>
</orders>

标签： sqlsql-serverxmlxml-parsing

解决方案

从您的问题中，有一件事不清楚：输出列的命名。

在您的预期输出中，它们的名称类似于attribute-id. 但是在您的评论中听起来，就像您选择了前 4 个属性，而您想省略其余的。

我想展示两种方法，选择你更喜欢的一种：

DECLARE @mockupTable TABLE(ID INT IDENTITY, YourXml XML);
INSERT INTO @mockupTable VALUES
(N'<orders xmlns="www address">
    <order>
        <order-date>2019-09-05</order-date>
        <created-by>storefront</created-by>
        <original-order-no>000001</original-order-no>
        <currency>USD</currency>
        <taxation>gross</taxation>
        <invoice-no>0099999</invoice-no>
        <custom-attributes>
            <custom-attribute attribute-id="t01">c01</custom-attribute>
            <custom-attribute attribute-id="t02">c02</custom-attribute>
            <custom-attribute attribute-id="t03">c03</custom-attribute>
            <custom-attribute attribute-id="t04">c04</custom-attribute>
        </custom-attributes>    
    </order>
</orders>');

--此查询将使用attribute-id来选择相应的属性。
--我们可以用相同的名字保存返回这个
--如果你的xml没有对应的属性，就会有一个NULL值

WITH XMLNAMESPACES(DEFAULT 'www address')
SELECT o.value('(order-date/text())[1]','date') OrderDate
      --As in your other questions
      ,o.value('(custom-attributes/custom-attribute[@attribute-id="t01"]/text())[1]','varchar(100)') AS t01 
      ,o.value('(custom-attributes/custom-attribute[@attribute-id="t02"]/text())[1]','varchar(100)') AS t02
      ,o.value('(custom-attributes/custom-attribute[@attribute-id="t03"]/text())[1]','varchar(100)') AS t03 
      ,o.value('(custom-attributes/custom-attribute[@attribute-id="t04"]/text())[1]','varchar(100)') AS t04 
FROM @mockupTable t
CROSS APPLY t.YourXml.nodes('/orders/order') A(o);

——这个比较简单。它只会选择前四个属性，无论它们有什么 id。

WITH XMLNAMESPACES(DEFAULT 'www address')
SELECT o.value('(order-date/text())[1]','date') OrderDate
      --As in your other questions
      ,o.value('(custom-attributes/custom-attribute[1]/text())[1]','varchar(100)') AS ca1 
      ,o.value('(custom-attributes/custom-attribute[2]/text())[1]','varchar(100)') AS ca2
      ,o.value('(custom-attributes/custom-attribute[3]/text())[1]','varchar(100)') AS ca3 
      ,o.value('(custom-attributes/custom-attribute[4]/text())[1]','varchar(100)') AS ca4 
FROM @mockupTable t
CROSS APPLY t.YourXml.nodes('/orders/order') A(o);

推荐阅读