October 4, 2013 at 8:48 am
Following the advice given in this thread, I'm trying to extract data from XML in a hierarchy - the query below is nearly working, but where a node contains no data in itself, just child nodes, the [val] column being returned contains the concatenated data from all child nodes - is there any way I can prevent this?
For example, the node <AvailableTrains> contains no data itself, but holds a set of <availableTrain> nodes - which can be many, but in the example data is only showing one available tran for each available route.
DECLARE @SAMPLE TABLE (id int, item_xml xml)
insert into
@SAMPLE (id, item_xml)
values (1, '<ArrayOfAvailableRoute>
<AvailableRoute>
<RouteId>3d85cb24-fa0c-4807-a749-3e50c9ac9e8d</RouteId>
<RequestedTrainId>d8610571-0125-4496-9897-b784d8e1a6fe</RequestedTrainId>
<AvailableTrains>
<AvailableTrain>
<DepartureLocation>
<StationId>EUS</StationId>
<StationName>London Euston</StationName>
</DepartureLocation>
<ArrivalLocation>
<StationId>GLS</StationId>
<StationName>Glasgow Central</StationName>
<CityId>GLS</CityId>
<CityName>Glasgow</CityName>
</ArrivalLocation>
<DepartureDateTime>2013-08-26T13:10:00</DepartureDateTime>
<ArrivalDateTime>2013-08-26T19:40:00</ArrivalDateTime>
<TrainId>52ac75e1-0ca9-4b6a-b372-856224f5527d</TrainId>
<TrainDetail>
<TrainNumber>
<Operator>
<OperatorId>BR</OperatorId>
<OperatorName>British Rail </OperatorName>
</Operator>
<Number>2676</Number>
</TrainNumber>
<OperatingOperator>
<OperatorId>BA</OperatorId>
<OperatorName>British Rail </OperatorName>
</OperatingOperator>
<NumberOfStops>0</NumberOfStops>
<TrainDuration>04:30</TrainDuration>
</TrainDetail>
</AvailableTrain>
</AvailableTrains>
</AvailableRoute>
<AvailableRoute>
<RouteId>710b2139-bb34-404b-aab3-2b7c906c2206</RouteId>
<RequestedTrainId>19f1cc5e-5a7c-4560-bf2b-62a6cbb3c99d</RequestedTrainId>
<AvailableTrains>
<AvailableTrain>
<DepartureLocation>
<StationId>GLS</StationId>
<StationName>Glasgow</StationName>
</DepartureLocation>
<ArrivalLocation>
<StationId>LGW</StationId>
<StationName>London Euston</StationName>
</ArrivalLocation>
<DepartureDateTime>2013-09-02T20:40:00</DepartureDateTime>
<ArrivalDateTime>2013-09-02T23:35:00</ArrivalDateTime>
<TrainId>12f809f2-4fbc-4480-9877-e29f378bf7b2</TrainId>
<TrainDetail>
<TrainNumber>
<Operator>
<OperatorId>BR</OperatorId>
<OperatorName>British Rail </OperatorName>
</Operator>
<Number>2677</Number>
</TrainNumber>
<OperatingOperator>
<OperatorId>BR</OperatorId>
<OperatorName>British Rail </OperatorName>
</OperatingOperator>
<NumberOfStops>0</NumberOfStops>
<TrainDuration>04:55</TrainDuration>
</TrainDetail>
</AvailableTrain>
</AvailableTrains>
</AvailableRoute>
</ArrayOfAvailableRoute>')
SELECT
id,
T.n.value('localname[1]', 'varchar(100)') AS [AttributeName]
,T.n.value('parent[1]', 'VARCHAR(100)') AS [parent]
,T.n.value('value[1]', 'VARCHAR(max)') AS [val]
FROM (
SELECT top 10 id
,item_xml.query('
for $node in /descendant::node()[local-name() != ""]
return <node>
<localname>{ local-name($node) }</localname>
<parent>{ local-name($node/..) }</parent>
<value>{ $node }</value>
</node>') AS nodes
FROM @SAMPLE
) q1
CROSS APPLY q1.nodes.nodes('/node') AS T(n)
October 6, 2013 at 2:44 pm
You might want to have a look at this link
It'll show you an alternative how to shred the data. The code should be reduced to the information required to avoid overhead.
October 7, 2013 at 1:35 am
That looks perfect!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply