Azure SQL Server中如何用T-SQL解析Trias XML并转换为JSON？

在Azure SQL Server环境下完全可以实现XML到JSON的转换，同时你也可以直接通过T-SQL解析XML得到你需要的公交时刻记录集，无需额外中转JSON，两种场景的实现代码如下：

完整T-SQL示例 #

-- 声明XML变量存储你的Trias数据
DECLARE @TriasXML XML = '<?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>'

-- 声明命名空间，解析XML并转JSON
;WITH XMLNAMESPACES (
    'http://www.vdv.de/trias' AS trias,
    'http://www.siri.org.uk/siri' AS siri
)
SELECT 
    siri:ResponseTimestamp.value('.', 'datetime2') AS ResponseTime,
    StopEvent.value('(trias:ThisCall/trias:CallAtStop/trias:ServiceDeparture/trias:TimetabledTime)[1]', 'datetime2') AS TimetabledDeparture,
    StopEvent.value('(trias:ThisCall/trias:CallAtStop/trias:ServiceDeparture/trias:EstimatedTime)[1]', 'datetime2') AS EstimatedDeparture,
    StopEvent.value('(trias:Service/trias:PublishedLineName/trias:Text)[1]', 'varchar(10)') AS LineNumber,
    StopEvent.value('(trias:Service/trias:PublishedLineName/trias:Language)[1]', 'varchar(5)') AS Language
FROM 
    @TriasXML.nodes('/trias:Trias/trias:ServiceDelivery') AS SD(ServiceDelivery)
CROSS APPLY
    ServiceDelivery.nodes('trias:DeliveryPayload/trias:StopEventResponse/trias:StopEventResult/trias:StopEvent') AS SE(StopEvent)
-- 去掉下面这行就直接返回结构化记录集，不需要转JSON
FOR JSON PATH, ROOT('BusSchedules')

输出说明 #

• 保留最后一行FOR JSON代码时，会输出标准JSON结构，示例如下：

{
  "BusSchedules": [
    {
      "ResponseTime": "2021-11-25T17:52:12",
      "TimetabledDeparture": "2021-11-25T17:53:00",
      "EstimatedDeparture": "2021-11-25T17:53:00",
      "LineNumber": "58",
      "Language": "de"
    },
    {
      "ResponseTime": "2021-11-25T17:52:12",
      "TimetabledDeparture": "2021-11-25T17:58:00",
      "EstimatedDeparture": "2021-11-25T17:58:00",
      "LineNumber": "60",
      "Language": "de"
    }
  ]
}

• 删掉最后一行FOR JSON代码时，会直接返回符合你需求的公交时刻记录集表。

内容的提问来源于stack exchange，提问作者Mara