Warm tip: This article is reproduced from serverfault.com, please click
sql-server tsql xml xquery shred

Shredding XML in SQL but cross apply adds unwanted records

发布于 2020-12-01 02:36:25

I have to flatten out this XML source into one SQL server table. There are multiple ResponseID per source file and multiple TextAnalyticsItem per ResponseID. I want to limit the records so that I only get the TextAnalyticsItems related to the ResponseID. However, the cross apply method gives me all ResponseId with all TextAnalyticsItem. How do I prevent the additional records?

DECLARE @XMLToParse XML;
SET @XMLToParse = '  
<Responses>
  <Response>
    <ResponseId>7662934</ResponseId>
    <SurveyId>123</SurveyId>
    <RespondentId>234909</RespondentId>
    <QuestionId>141757</QuestionId>
    <ScaleId>3401</ScaleId>
    <AnswerId>17130</AnswerId>
    <ResponseMemo>Useful</ResponseMemo>
    <ResponseRank>0</ResponseRank>
    <ResponseState>0</ResponseState>
    <CompletedDate>2020-07-06T09:07:40</CompletedDate>
    <ModifiedDate>2020-07-06T09:07:41</ModifiedDate>
    <LanguageId>220</LanguageId>
    <ResponseNum>0</ResponseNum>
    <ResponseDate />
    <TextAnalyticsData>
      <TextAnalyticsItem>
        <Level1>Values Standards</Level1>
        <Level2>Better/Best/Brilliant</Level2>
        <Level3>Positive</Level3>
        <Sentiment>1</Sentiment>
      </TextAnalyticsItem>
      <TextAnalyticsItem>
        <Level1>All-Behaviors</Level1>
        <Level2>Positive_</Level2>
        <Sentiment>1</Sentiment>
      </TextAnalyticsItem>
    </TextAnalyticsData>
  </Response>
   <Response>
    <ResponseId>7662078</ResponseId>
    <SurveyId>123</SurveyId>
    <RespondentId>234826</RespondentId>
    <QuestionId>141756</QuestionId>
    <ScaleId>3400</ScaleId>
    <AnswerId>17129</AnswerId>
    <ResponseMemo>Ghjlkk</ResponseMemo>
    <ResponseRank>0</ResponseRank>
    <ResponseState>0</ResponseState>
    <CompletedDate>2020-07-03T07:17:31</CompletedDate>
    <ModifiedDate>2020-07-03T07:17:31</ModifiedDate>
    <LanguageId>220</LanguageId>
    <ResponseNum>0</ResponseNum>
    <ResponseDate />
    <TextAnalyticsData>
      <TextAnalyticsItem>
        <Level1>Nonactionable</Level1>
        <Sentiment>0</Sentiment>
      </TextAnalyticsItem>
    </TextAnalyticsData>
  </Response>
</Responses>'
 SELECT xmlData.A.value('ResponseId[1]', 'VARCHAR(100)') AS ResponseId, 
       xmlData.A.value('SurveyId[1]', 'VARCHAR(100)') AS SurveyId, 
       xmlData.A.value('RespondentId[1]', 'VARCHAR(100)') AS RespondentId, 
       xmlData.A.value('TextAnalyticsData[1]', 'VARCHAR(100)') AS TextAnalyticsData, 
       tbl1.TxtItems.value('Level1[1]', 'VARCHAR(100)') AS Level1, 
       tbl1.TxtItems.value('Level2[1]', 'VARCHAR(100)') AS Level2, 
       tbl1.TxtItems.value('Level3[1]', 'VARCHAR(100)') AS Level3, 
       tbl1.TxtItems.value('Sentiment[1]', 'VARCHAR(100)') AS Sentiment
FROM @XMLToParse.nodes('Responses/Response/TextAnalyticsData/TextAnalyticsItem') tbl1(TxtItems)
     cross apply @XMLToParse.nodes('Responses/Response') xmlData(A)

ORDER BY ResponseId, 
         RespondentId;

So instead of

ResponseId        SurveyId          RespondentId      TextAnalyticsData                                                     Level1            Level2            Level3     Sentiment
---------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------
7662078           123               234826            Nonactionable0                                                        Values Standards  Better/Best/BrilliPositive          1
7662078           123               234826            Nonactionable0                                                        All-Behaviors     Positive_         NULL              1
7662078           123               234826            Nonactionable0                                                        Nonactionable     NULL              NULL              0
7662934           123               234909            Values StandardsBetter/Best/BrilliantPositive1All-BehaviorsPositive_1 Values Standards  Better/Best/BrilliPositive          1
7662934           123               234909            Values StandardsBetter/Best/BrilliantPositive1All-BehaviorsPositive_1 Nonactionable     NULL              NULL              0
7662934           123               234909            Values StandardsBetter/Best/BrilliantPositive1All-BehaviorsPositive_1 All-Behaviors     Positive_         NULL              1

I want to get

ResponseId        SurveyId          RespondentId      TextAnalyticsData                                                     Level1            Level2            Level3     Sentiment
---------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------
7662078           123               234826            Nonactionable0                                                        Nonactionable     NULL              NULL              0
7662934           123               234909            Values StandardsBetter/Best/BrilliantPositive1All-BehaviorsPositive_1 Values Standards  Better/Best/BrilliPositive          1
7662934           123               234909            Values StandardsBetter/Best/BrilliantPositive1All-BehaviorsPositive_1 All-Behaviors     Positive_         NULL              1

Any suggestions? Thanks

Questioner
Fraser
Viewed
0
分享
Yitzhak Khabinsky 2020-12-01 11:15:59

Check it out how to achieve what you need. It simulates one-to-many relationship.

I am not sure what was the intent for the TextAnalyticsData column. That's why I commented it out.

SQL

DECLARE @XMLToParse XML = 
N'<Responses>
  <Response>
    <ResponseId>7662934</ResponseId>
    <SurveyId>123</SurveyId>
    <RespondentId>234909</RespondentId>
    <QuestionId>141757</QuestionId>
    <ScaleId>3401</ScaleId>
    <AnswerId>17130</AnswerId>
    <ResponseMemo>Useful</ResponseMemo>
    <ResponseRank>0</ResponseRank>
    <ResponseState>0</ResponseState>
    <CompletedDate>2020-07-06T09:07:40</CompletedDate>
    <ModifiedDate>2020-07-06T09:07:41</ModifiedDate>
    <LanguageId>220</LanguageId>
    <ResponseNum>0</ResponseNum>
    <ResponseDate />
    <TextAnalyticsData>
      <TextAnalyticsItem>
        <Level1>Values Standards</Level1>
        <Level2>Better/Best/Brilliant</Level2>
        <Level3>Positive</Level3>
        <Sentiment>1</Sentiment>
      </TextAnalyticsItem>
      <TextAnalyticsItem>
        <Level1>All-Behaviors</Level1>
        <Level2>Positive_</Level2>
        <Sentiment>1</Sentiment>
      </TextAnalyticsItem>
    </TextAnalyticsData>
  </Response>
   <Response>
    <ResponseId>7662078</ResponseId>
    <SurveyId>123</SurveyId>
    <RespondentId>234826</RespondentId>
    <QuestionId>141756</QuestionId>
    <ScaleId>3400</ScaleId>
    <AnswerId>17129</AnswerId>
    <ResponseMemo>Ghjlkk</ResponseMemo>
    <ResponseRank>0</ResponseRank>
    <ResponseState>0</ResponseState>
    <CompletedDate>2020-07-03T07:17:31</CompletedDate>
    <ModifiedDate>2020-07-03T07:17:31</ModifiedDate>
    <LanguageId>220</LanguageId>
    <ResponseNum>0</ResponseNum>
    <ResponseDate />
    <TextAnalyticsData>
      <TextAnalyticsItem>
        <Level1>Nonactionable</Level1>
        <Sentiment>0</Sentiment>
      </TextAnalyticsItem>
    </TextAnalyticsData>
  </Response>
</Responses>';

 SELECT r.value('(ResponseId/text())[1]', 'VARCHAR(100)') AS ResponseId
    , r.value('(SurveyId/text())[1]', 'VARCHAR(100)') AS SurveyId
    , r.value('(RespondentId/text())[1]', 'VARCHAR(100)') AS RespondentId
       --xmlData.A.value('TextAnalyticsData[1]', 'VARCHAR(100)') AS TextAnalyticsData, 
    , a.value('(Level1/text())[1]', 'VARCHAR(100)') AS Level1
    , a.value('(Level2/text())[1]', 'VARCHAR(100)') AS Level2
    , a.value('(Level3/text())[1]', 'VARCHAR(100)') AS Level3
    , a.value('(Sentiment/text())[1]', 'VARCHAR(100)') AS Sentiment
FROM @XMLToParse.nodes('/Responses/Response') t1(r)
     CROSS APPLY t1.r.nodes('TextAnalyticsData/TextAnalyticsItem[Level1/text()]') t2(a)
--ORDER BY ResponseId, 
--         RespondentId;

Output

+------------+----------+--------------+------------------+-----------------------+----------+-----------+
| ResponseId | SurveyId | RespondentId |      Level1      |        Level2         |  Level3  | Sentiment |
+------------+----------+--------------+------------------+-----------------------+----------+-----------+
|    7662934 |      123 |       234909 | Values Standards | Better/Best/Brilliant | Positive |         1 |
|    7662934 |      123 |       234909 | All-Behaviors    | Positive_             | NULL     |         1 |
|    7662078 |      123 |       234826 | Nonactionable    | NULL                  | NULL     |         0 |
+------------+----------+--------------+------------------+-----------------------+----------+-----------+
Yitzhak Khabinsky 2020-12-01 03:46:53

@Fraser, glad to hear that the proposed solution is working for you. Please connect with me on LinkedIn.

热门帖子

1
MES给制造业带来看得见的效益
2
Casvisor：带 Web UI 的开源堡垒机，支持 RDP、VNC、SSH 远程桌面、主机和数据库管理、批量执行命令、单点登录
3
深圳这不考虑通勤的情况下，哪里租金最便宜呢。
4
杭州余杭这边除了三甲求安利洗牙的私立牙科
5
安装一个 2 年前老版本的微信。iphone13 体验大增。十分后悔升级 ios 版本
6
今天碰到的一个看似简单的算法问题卡的我头疼：余额和构成余额的交易的问题，问 GPT 也没结果
7
有经常打果蔬汁和绿拿铁的吗？
8
为什么 Apple TV 平台下为什么免费好用的视频播放器都没有？
9
你们是否意识到人生没有什么保障？
10
中国的男女关系会朝向哪个方向发展呢？日韩法？

热门github

1
Implementation of paper - YOLOv9: Learning What You Want to Learn Using Programmable Gradient Information
2
A Windows and Office activator using HWID / Ohook / KMS38 / Online KMS activation methods, with a focus on open-source code and fewer antivirus detections.
3
Get up and running with Llama 2, Mistral, Gemma, and other large language models.
4
该项目可以让你通过订阅的方式使用Cloudflare WARP+，自动获取流量。This project enables you to use Cloudflare WARP+ through subscription, automatically acquiring traffic.
5
Multi functional app to find duplicates, empty folders, similar images etc.
6
Xray panel supporting multi-protocol multi-user expire day & traffic & ip limit (Vmess & Vless & Trojan & ShadowSocks & Wireguard)
7
The Free Software Media System
8
lightweight, standalone C++ inference engine for Google's Gemma models.
9
📚 Freely available programming books
10
A collective list of free APIs
11
1️⃣🐝🏎️ The One Billion Row Challenge -- A fun exploration of how quickly 1B rows from a text file can be aggregated with Java
12
🎓 Path to a free self-taught education in Computer Science!
13
Curso para aprender el lenguaje de programación Python desde cero y para principiantes. 75 clases, 37 horas en vídeo, código, proyectos y grupo de chat. Fundamentos, frontend, backend, testing, IA...
14
This repository contains System Design resources which are useful while preparing for interviews and learning Distributed Systems
15
Mamba is a new state space model architecture showing promising performance on information-dense data such as language modeling, where previous subquadratic models fall short of Transformers. It is based on the line of progress on structured state space models, with an efficient hardware-aware design and implementation in the spirit of FlashAttention.