Extractinng node details from XML data

• Chris Quinn-821458

  Hall of Fame

  Points: 3192

  More actions

  October 4, 2013 at 8:48 am

  #395609

  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)

• LutzM

  SSC Guru

  Points: 107049

  More actions

  October 6, 2013 at 2:44 pm

  #1656192

  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[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• Chris Quinn-821458

  Hall of Fame

  Points: 3192

  More actions

  October 7, 2013 at 1:35 am

  #1656232

  That looks perfect!