#sql-server #xml #tsql #xquery #shred
#sql-server #xml #tsql #xquery #измельчить
Вопрос:
Я должен сгладить этот источник XML в одну таблицу SQL server. Для каждого исходного файла имеется несколько идентификаторов ответа и несколько элементов TextAnalyticsItem для каждого идентификатора ответа. Я хочу ограничить записи, чтобы я получал только TextAnalyticsItems, связанные с ResponseID. Однако метод перекрестного применения дает мне все ResponseId со всеми TextAnalyticsItem. Как мне предотвратить дополнительные записи?
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;
Поэтому вместо
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
Я хочу получить
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
Есть предложения? Спасибо
Ответ №1:
Проверьте, как достичь того, что вам нужно. Он имитирует отношение «один ко многим».
Я не уверен, что было целью для столбца TextAnalyticsData. Вот почему я прокомментировал это.
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;
Вывод
------------ ---------- -------------- ------------------ ----------------------- ---------- -----------
| 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 |
------------ ---------- -------------- ------------------ ----------------------- ---------- -----------
Комментарии:
1. @Фрейзер, рад слышать, что предложенное решение работает для вас. Пожалуйста, свяжитесь со мной в LinkedIn.