問題描述

如何以格式返回數據:

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>

推薦答案

從你的問題來看，有一點不清楚:輸出列的命名.

From your question there's one thing not clear: The naming of the output columns.

在您預期的輸出中，它們的名稱與它們的 attribute-id 一樣.但在您的評論中，聽起來像是您選擇了前 4 個屬性，而您想忽略其余屬性.

In your expected output they are named like their attribute-id. But in your comments it sounds, like you are picking the first 4 attributes and you want to omit the rest.

我想展示兩種方法，選擇你更喜歡的一種:

I want to show two approaches, pick the one you like more:

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值

--This query will use the attribute-id to pick the corresponding attribute.
--We can savely return this with the same name
--If your XML does not have the corresponding attribute, there will be a NULL value

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.

--This one is easier. It will pick just the first four attributes, no matter what id they have.

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);

這篇關于sql server 上的 XML 解析的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！