我花了幾個小時試圖決議這個 XML（公交車站時間表）并生成一個帶有 , 的記錄集。有沒有辦法將 XML 轉換為 JSON，我發現它更容易處理？
有人愿意幫忙嗎？(Azure SQL Server)
<?xml version="1.0" encoding="UTF-8"?>
<Trias xmlns="http://www.vdv.de/trias" version="1.1">
<ServiceDelivery>
<ResponseTimestamp xmlns="http://www.siri.org.uk/siri">2021-11-25T17:52:12Z</ResponseTimestamp>
<DeliveryPayload>
<StopEventResponse>
<StopEventResult>
<StopEvent>
<ThisCall>
<CallAtStop>
<ServiceDeparture>
<TimetabledTime>2021-11-25T17:53:00Z</TimetabledTime>
<EstimatedTime>2021-11-25T17:53:00Z</EstimatedTime>
</ServiceDeparture>
</CallAtStop>
</ThisCall>
<Service>
<PublishedLineName>
<Text>58</Text>
<Language>de</Language>
</PublishedLineName>
</Service>
</StopEvent>
</StopEventResult>
<StopEventResult>
<StopEvent>
<ThisCall>
<CallAtStop>
<ServiceDeparture>
<TimetabledTime>2021-11-25T17:58:00Z</TimetabledTime>
<EstimatedTime>2021-11-25T17:58:00Z</EstimatedTime>
</ServiceDeparture>
</CallAtStop>
</ThisCall>
<Service>
<PublishedLineName>
<Text>60</Text>
<Language>de</Language>
</PublishedLineName>
</Service>
</StopEvent>
</StopEventResult>
</StopEventResponse>
</DeliveryPayload>
</ServiceDelivery>
</Trias>
uj5u.com熱心網友回復：
沒有提供最小的可重現示例。
所以從臀部射擊。
不需要任何 XML 決議。SQL Server 帶有內置的 XQuery 語言支持來處理 XML 資料型別。
唯一的細微差別是輸入 XML 具有名稱空間。
- 默認命名空間是通過 using
XMLNAMESPACES()子句宣告的。 - 一對夫婦的XQuery的方法是使用：
.nodes()和.value()
查詢陳述句
DECLARE @xml XML =
N'<Trias xmlns="http://www.vdv.de/trias" version="1.1">
<ServiceDelivery>
<ResponseTimestamp xmlns="http://www.siri.org.uk/siri">2021-11-25T17:52:12Z</ResponseTimestamp>
<DeliveryPayload>
<StopEventResponse>
<StopEventResult>
<StopEvent>
<ThisCall>
<CallAtStop>
<ServiceDeparture>
<TimetabledTime>2021-11-25T17:53:00Z</TimetabledTime>
<EstimatedTime>2021-11-25T17:53:00Z</EstimatedTime>
</ServiceDeparture>
</CallAtStop>
</ThisCall>
<Service>
<PublishedLineName>
<Text>58</Text>
<Language>de</Language>
</PublishedLineName>
</Service>
</StopEvent>
</StopEventResult>
<StopEventResult>
<StopEvent>
<ThisCall>
<CallAtStop>
<ServiceDeparture>
<TimetabledTime>2021-11-25T17:58:00Z</TimetabledTime>
<EstimatedTime>2021-11-25T17:58:00Z</EstimatedTime>
</ServiceDeparture>
</CallAtStop>
</ThisCall>
<Service>
<PublishedLineName>
<Text>60</Text>
<Language>de</Language>
</PublishedLineName>
</Service>
</StopEvent>
</StopEventResult>
</StopEventResponse>
</DeliveryPayload>
</ServiceDelivery>
</Trias>';
;WITH XMLNAMESPACES(DEFAULT 'http://www.vdv.de/trias')
SELECT c.value('(ThisCall/CallAtStop/ServiceDeparture/TimetabledTime/text())[1]', 'DATETIMEOFFSET(0)') AS TimetabledTime
, c.value('(ThisCall/CallAtStop/ServiceDeparture/EstimatedTime/text())[1]', 'DATETIMEOFFSET(0)') AS EstimatedTime
, c.value('(Service/PublishedLineName/Text/text())[1]', 'VARCHAR(100)') AS [Text]
, c.value('(Service/PublishedLineName/Language/text())[1]', 'CHAR(2)') AS [Language]
FROM @xml.nodes('/Trias/ServiceDelivery/DeliveryPayload/StopEventResponse/StopEventResult/StopEvent') AS t(c);
輸出
---------------------------- ---------------------------- ------ ----------
| TimetabledTime | EstimatedTime | Text | Language |
---------------------------- ---------------------------- ------ ----------
| 2021-11-25 17:53:00 00:00 | 2021-11-25 17:53:00 00:00 | 58 | de |
| 2021-11-25 17:58:00 00:00 | 2021-11-25 17:58:00 00:00 | 60 | de |
---------------------------- ---------------------------- ------ ----------
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/366939.html
標籤：sql-server xml 查询语句 查询
下一篇：如何使用子查詢創建新列