在SQLServer中決議XML（用一個陣列）。 -有解無憂

我試圖在SQL Server中決議一些XML，我已經開發了一些代碼來做到這一點，但回傳的一個資料項是以陣列的形式出現的？

SQL 到目前為止，以XML為例...

DECLARE @XML XML
SET @XML = '<?xml version="1.0" encoding="UTF-8"? >
<feedback-items>/span>
<feedback-item id="1001"/span>>
<message>該訊息</message>
<sentiment-score>3</sentiment-score> 
<channel>/span>SMS</channel>
<structured-fields>/span>
<structured-field>/span>
<name>/span>loyalty_card_number</name>
<value>123456</value> 
</structured-field>/span>
<structured-field>/span>
<name>/span>given_score</name>/span>
<value>4</value>/span>
</structured-field>/span>
</structured-fields>/span>
<categories>/span>
<category>/span>People</category>
<category>/span>Process</category>/span>
<category>Product</category>
<category>/span>Place</category>/span>
</category>/span>
<insights>/span>
<insight>/span>
<category>/span>People</category>/span>
<sentiment-score>1</sentiment-score> 
</insight>/span>
</insights>/span>
<notes>/span>
<notes> 
<id>1</id>/span>
<username>Bob</username> 
<created>2012-12-11 09:00:00</created>
<content>客戶很高興</content>/span>
</note>/span>
</notes>/span>
</feedback-item>/span>
<feedback-item id="1002"/span>>
<message>該訊息</message>
<sentiment-score>3</sentiment-score> 
<channel>/span>SMS</channel>
<structured-fields>/span>
<structured-field>/span>
<name>/span>loyalty_card_number</name>
<value>123456</value> 
</structured-field>/span>
<structured-field>/span>
<name>/span>given_score</name>/span>
<value>6</value>/span>
</structured-field>/span>
</structured-fields>/span>
<categories>/span>
<category>/span>People</category>
<category>/span>Process</category>/span>
<category>Product</category>
<category>/span>Place</category>/span>
</category>/span>
<insights>/span>
<insight>/span>
<category>/span>People</category>/span>
<sentiment-score>1</sentiment-score> 
</insight>/span>
</insights>/span>
<notes>/span>
<notes> 
<id>1</id>/span>
<username>Mike</username> 
<created>2012-12-12 09:00:00</created>
<content>客戶很滿意</content>
</note>/span>
</notes>/span>
</feedback-item>/span>
</feedback-items>/span>
'

選擇 
    xmlData.A.value('@id','INT') AS [FeedbackItem]
    --fields.A.value('./Name/text())[1]','Varchar(50)') AS [Name] 。
    --xmlData.A.value('(./structured-fields/structured-field/Name/text())[1]','Varchar(50)') AS [Name]
FROM @XML.nodes('feedback-items/feedback-item') xmlData(A)
        --CROSS APPLY xmlData.A.nodes('/structured-fields/structured-field') AS fields(A)

期望的輸出...

。渠道給定分數用戶名創建內容

反饋專案	資訊	情緒分數	會員卡號
1001	3

SMS123456Bob 2012-12-11 客戶很高興客戶很高興客戶很高興

10022012-12-12 客戶很滿意

uj5u.com熱心網友回復：

請嘗試以下解決方案。

你的XPath運算式有偏差。

你的XPath運算式有很大偏差。

SQL

DECLARE @xml XML =
N'<feedback-items> 
    <feedback-item id="1001"/span>>
        <message>該訊息</message>
        <sentiment-score>3</sentiment-score> 
        <channel>/span>SMS</channel>
        <structured-fields>/span>
            <structured-field>/span>
                <name>/span>loyalty_card_number</name>
                <value>123456</value> 
            </structured-field>/span>
            <structured-field>/span>
                <name>/span>given_score</name>/span>
                <value>4</value>/span>
            </structured-field>/span>
        </structured-fields>/span>
        <categories>/span>
            <category>/span>People</category>
            <category>/span>Process</category>/span>
            <category>Product</category>
            <category>/span>Place</category>/span>
        </category>/span>
        <insights>/span>
            <insight>/span>
                <category>/span>People</category>/span>
                <sentiment-score>1</sentiment-score> 
            </insight>/span>
        </insights>/span>
        <notes>/span>
            <notes> 
                <id>1</id>/span>
                <username>Bob</username> 
                <created>2012-12-11 09:00:00</created>
                <content>客戶很高興</content>/span>
            </note>/span>
        </notes>/span>
    </feedback-item>/span>
    <feedback-item id="1002"/span>>
        <message>該訊息</message>
        <sentiment-score>3</sentiment-score> 
        <channel>/span>SMS</channel>
        <structured-fields>/span>
            <structured-field>/span>
                <name>/span>loyalty_card_number</name>
                <value>123456</value> 
            </structured-field>/span>
            <structured-field>/span>
                <name>/span>given_score</name>/span>
                <value>6</value>/span>
            </structured-field>/span>
        </structured-fields>/span>
        <categories>/span>
            <category>/span>People</category>
            <category>/span>Process</category>/span>
            <category>Product</category>
            <category>/span>Place</category>/span>
        </category>/span>
        <insights>/span>
            <insight>/span>
                <category>/span>People</category>/span>
                <sentiment-score>1</sentiment-score> 
            </insight>/span>
        </insights>/span>
        <notes>/span>
            <notes> 
                <id>1</id>/span>
                <username>Mike</username> 
                <created>2012-12-12 09:00:00</created>
                <content>客戶很滿意</content>
            </note>/span>
        </notes>/span>
    </feedback-item>/span>
</feedback-items>/span>
';

SELECT a.value('@id','INT') AS [FeedbackItem] 。
    , a.value('(message/text())[1]','Varchar(50)') AS [Message] 。
    , a.value('(sentiment-score/text())[1]','Varchar(50)') AS [SentimentScore] 。
    , a.value('(channel/text())[1]', 'Varchar(50)') AS [channel] 。
    , a.value('(structuredd-fields/structured-field[name="loyalty_card_number"]/value/text())[1]', 'Varchar(50)') AS [loyalty_card_number]
    , a.value('(structuredd-fields/structured-field[name="given_score"]/value/text())[1]', 'Varchar(50)') AS [GivenScore]
    , b.value('(username/text())[1]','Varchar(50)') AS [Username] 。
    , b.value('(credated/text())[1]','DATE') AS [created]
    , b.value('(content/text())[1]','VARCHAR(100)') AS [content]
FROM @XML.nodes('/feedback-items/feedback-item') t1(a)
    CROSS APPLY t1.a.nodes('notes/note') AS t2(b)。

輸出

 -------------- ------------- ---------------- --------- --------------------- ------------ ---------- ------------ ------------------------ 
| FeedbackItem | Message | SentimentScore | channel | |     string">Username | created | content |
 -------------- ------------- ---------------- --------- --------------------- ------------ ---------- ------------ ------------------------ 
| 1001 | The message | 3 | SMS | 123456 | 4 | Bob | 2012-12-11 | The customer was happy |
| 1002 | The message | 3 | SMS | 123456 | 6 | Mike | 2012-12-12 | The customer was happy |
 -------------- ------------- ---------------- --------- --------------------- ------------ ---------- ------------ ------------------------

uj5u.com熱心網友回復：

你可以使用以下代碼

SELECT
  [反饋專案] = fb.value('@id','int') 。
  訊息 = fb.value('(message/text())[1]','nvarchar(200)')。
  [情感分數] = fb.value('(sentiment-score/text())[1]','int') 。
  渠道 = fb.value('(channel/text())[1]','nvarchar(200)')。
  [忠誠卡號] = fb.value('(structuredd-fields/structured-field[name[text()="loyalty_card_number"]]/value/text() [1]','nvarchar(200)')。
  [給定分數] = fb.value('(structured-fields/structured-field[name[text()="given_score"]/value/text())[1]','nvarchar(200)') 。)
  用戶名 = fb.value('(notes/note/username/text())[1]','nvarchar(200)') 。
  創建 = fb.value('(notes/note/created/text())[1]','datetime') 。
  內容 = fb.value('(notes/note/content/text())[1]','nvarchar(200) ')
FROM @xml.nodes(' feedback-items/feedback-item') x(fb)

db<>fiddle/a>

進一步說明：

XML被截斷了，我在其中增加了一些內容，以獲得理想的結果。我假設你有更多的內容。
注意structured-field是如何在name/text()上被過濾的，然后value/text(0被檢索
notes/note看起來它可能包含多個專案，你可能想用另一個.nodes

轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/324051.html

標籤：

上一篇：當我試圖打開檔案.sumocfg時，Sumo沒有找到檔案.net.xml。

下一篇：基于令牌的認證MVC5