Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Extractinng node details from XML data Expand / Collapse
Author
Message
Posted Friday, October 4, 2013 8:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 15, 2014 2:26 AM
Points: 188, Visits: 773
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)

Post #1501624
Posted Sunday, October 6, 2013 2:44 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Sunday, September 7, 2014 11:27 PM
Points: 7,164, Visits: 13,257
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.




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1501959
Posted Monday, October 7, 2013 1:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 15, 2014 2:26 AM
Points: 188, Visits: 773
That looks perfect!
Post #1502008
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse