T-SQL, how to parse this XML?-CodePudding

I've spent hours trying to parse this XML (bus stop schedule) and produce a recordset with , . Is there a way to convert XML to JSON, which I find is easier to handle?

Anyone willing to help? (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>

CodePudding user response：

A minimal reproducible example was not provided.

So shooting from the hip.

There is no need for any XML parsing. SQL Server comes with the built-in XQuery language support to handle XML data type.

The only nuance is that the input XML has namespaces.

A default namespace is declared by using XMLNAMESPACES() clause.
A couple of XQuery methods are in use: .nodes() and .value()

SQL

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);

Output

 ---------------------------- ---------------------------- ------ ---------- 
|       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       |
 ---------------------------- ---------------------------- ------ ----------