RE: XML Shredding / parsing – SQLServerCentral

Eirikur Eiriksson

SSC Guru

Points: 182882

September 17, 2014 at 3:22 am

#1745982

This code returns the node structure of the XML

😎

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @TXML XML = N'<EssRoot>

<T.6.03.0>

<eventTS>2014-09-10T23:51:53.0Z</eventTS>

<equipment unitAddress="105328605" equipType="tractor" alias="0105328605" ID="0105328605" />

<position lat="42.76819444444445" lon="-78.59981481481482" posTS="2014-09-10T23:51:48.0Z" />

<posType>1</posType>

<ignitionStatus>1</ignitionStatus>

<tripStatus>O</tripStatus>

<ltdDistance>0.0</ltdDistance>

<speed>0</speed>

<heading>0</heading>

<workflowEventType>loadAtTerminal</workflowEventType>

<data id="X_XMM_FILTERED">

<datum name="sortId" value="100" />

<datum name="destSortId" value="1" />

<datum name="msgType" value="loadAtTerminal" />

<datum name="planId" value="TE00098" />

<datum name="destId" value="P12345" />

<data id="48611F45E900000000000000D046">

<datum name="type" value="loadAtTerminal" />

<datum name="trailerId" value="TR-1234" />

<data id="48611F45E900000000000000D049">

<datum name="type" value="evansDedicatedCommodityList" />

<data id="48611F45E900000000000000D051">

<datum name="commodityName" value="87 Octane Gasoline" />

<datum name="bolNo" value="BOL1" />

<datum name="grossGallons" value="1000" />

<datum name="type" value="evansDedicatedCommodityItem" />

<datum name="netGallons" value="900" />

<datum name="orderedGallons" value="1000" />

</data>

<data id="48611F45F800000000000000D052">

<datum name="commodityName" value="Regular Grade Gasoline" />

<datum name="bolNo" value="BOL2" />

<datum name="grossGallons" value="2000" />

<datum name="type" value="evansDedicatedCommodityItem" />

<datum name="netGallons" value="800" />

<datum name="orderedGallons" value="2000" />

</data>

<data id="48611F45F800000000000000D053">

<datum name="commodityName" value="Premium Grade Gasoline" />

<datum name="bolNo" value="BOL3" />

<datum name="grossGallons" value="3000" />

<datum name="type" value="evansDedicatedCommodityItem" />

<datum name="netGallons" value="700" />

<datum name="orderedGallons" value="3000" />

</data>

<data id="48611F45F800000000000000D054">

<datum name="commodityName" value="Regular Grade Gasoline" />

<datum name="bolNo" value="BOL4" />

<datum name="grossGallons" value="4000" />

<datum name="type" value="evansDedicatedCommodityItem" />

<datum name="netGallons" value="600" />

<datum name="orderedGallons" value="4000" />

</data>

<data id="48611F45F800000000000000D055">

<datum name="commodityName" value="87 Octane Gasoline" />

<datum name="bolNo" value="BOL5" />

<datum name="grossGallons" value="5000" />

<datum name="type" value="evansDedicatedCommodityItem" />

<datum name="netGallons" value="500" />

<datum name="orderedGallons" value="5000" />

</data>

<data id="48611F461800000000000000D056">

<datum name="ignition" value="true" />

<datum name="lifeToDateDistanceDisplayValue" value="0000000.0 MI" />

<datum name="ptoFuel" value="0" />

<datum name="type" value="vehicleServiceInfo" />

<datum name="isMoving" value="false" />

<datum name="currentDriver" value="" />

<datum name="returnAirSensor" value="00000000000000" />

<datum name="lifeToDateDistance" value="0000000.0" />

<datum name="inMotionUIEnabled" value="true" />

<datum name="odometer" value="0000000.0" />

<datum name="totalFuel" value="0" />

<datum name="parkedIdleFuel" value="0" />

<datum name="trailerID" value="" />

</data>

</T.6.03.0>

<T.6.03.0>

<eventTS>2014-09-10T23:53:57.0Z</eventTS>

<equipment unitAddress="105328605" equipType="tractor" alias="0105328605" ID="0105328605" />

<position lat="42.76819444444445" lon="-78.59986111111111" posTS="2014-09-10T23:53:52.0Z" />

<posType>1</posType>

<ignitionStatus>1</ignitionStatus>

<tripStatus>O</tripStatus>

<ltdDistance>0.0</ltdDistance>

<speed>0</speed>

<heading>0</heading>

<workflowEventType>unloadAtCustomer</workflowEventType>

<data id="X_XMM_FILTERED">

<datum name="sortId" value="200" />

<datum name="destSortId" value="1" />

<datum name="msgType" value="unloadAtCustomer" />

<datum name="planId" value="TE00098" />

<datum name="destId" value="P12345" />

<data id="4861212C6C00000000000000D084">

<datum name="beginInv" value="1000" />

<datum name="type" value="unloadAtCustomer" />

<datum name="reconcilliationComplete" value="true" />

<datum name="endInv" value="899" />

<data id="4861212C6C00000000000000D087">

<datum name="type" value="evansDedicatedCommodityList" />

<data id="4861212C6C00000000000000D089">

<datum name="open2" value="2000 " />

<datum name="close1" value="4000 " />

<datum name="water2" value="5000 " />

<datum name="type" value="evansDedicatedCommodityItem" />

<datum name="commodityName" value="87 Octane Gasoline" />

<datum name="water1" value="5000 " />

<datum name="grossGallons" value="2000" />

<datum name="open1" value="1000 " />

<datum name="netGallons" value="2000 " />

<datum name="open3" value="3000 " />

<datum name="orderedGallons" value="2000" />

<datum name="close2" value="4000 " />

<datum name="water3" value="5000 " />

<datum name="close3" value="4000 " />

</data>

<data id="4861212C8B00000000000000D090">

<datum name="ignition" value="true" />

<datum name="lifeToDateDistanceDisplayValue" value="0000000.0 MI" />

<datum name="ptoFuel" value="0" />

<datum name="type" value="vehicleServiceInfo" />

<datum name="isMoving" value="false" />

<datum name="currentDriver" value="" />

<datum name="returnAirSensor" value="00000000000000" />

<datum name="lifeToDateDistance" value="0000000.0" />

<datum name="inMotionUIEnabled" value="true" />

<datum name="odometer" value="0000000.0" />

<datum name="totalFuel" value="0" />

<datum name="parkedIdleFuel" value="0" />

<datum name="trailerID" value="" />

</data>

</T.6.03.0>

<T.6.03.0>

<eventTS>2014-09-11T00:09:39.0Z</eventTS>

<equipment unitAddress="105328605" equipType="tractor" alias="0105328605" ID="0105328605" />

<position lat="42.76819444444445" lon="-78.59981481481482" posTS="2014-09-11T00:09:34.0Z" />

<posType>1</posType>

<ignitionStatus>1</ignitionStatus>

<tripStatus>O</tripStatus>

<ltdDistance>0.0</ltdDistance>

<speed>0</speed>

<heading>0</heading>

<workflowEventType>startDelay</workflowEventType>

<data id="X_XMM_FILTERED">

<datum name="destTZOffset" value="-04:00" />

<datum name="msgType" value="startDelay" />

<data id="48612F89CA00000000000000D100">

<datum name="delayReason" value="Early" />

<datum name="type" value="delayTaskType" />

</data>

<data id="48612F89DA00000000000000D101">

<datum name="ignition" value="true" />

<datum name="lifeToDateDistanceDisplayValue" value="0000000.0 MI" />

<datum name="ptoFuel" value="0" />

<datum name="type" value="vehicleServiceInfo" />

<datum name="isMoving" value="false" />

<datum name="currentDriver" value="" />

<datum name="returnAirSensor" value="00000000000000" />

<datum name="lifeToDateDistance" value="0000000.0" />

<datum name="inMotionUIEnabled" value="true" />

<datum name="odometer" value="0000000.0" />

<datum name="totalFuel" value="0" />

<datum name="parkedIdleFuel" value="0" />

<datum name="trailerID" value="" />

</data>

</T.6.03.0>

<T.6.03.0>

<eventTS>2014-09-11T00:09:48.0Z</eventTS>

<equipment unitAddress="105328605" equipType="tractor" alias="0105328605" ID="0105328605" />

<position lat="42.76819444444445" lon="-78.59981481481482" posTS="2014-09-11T00:09:43.0Z" />

<posType>1</posType>

<ignitionStatus>1</ignitionStatus>

<tripStatus>O</tripStatus>

<ltdDistance>0.0</ltdDistance>

<speed>0</speed>

<heading>0</heading>

<workflowEventType>loadingDelay</workflowEventType>

<data id="X_XMM_FILTERED">

<datum name="destTZOffset" value="-04:00" />

<datum name="msgType" value="loadingDelay" />

<data id="48612FABC900000000000000D109">

<datum name="delayReason" value="Closed" />

<datum name="type" value="delayTaskType" />

</data>

<data id="48612FABD900000000000000D110">

<datum name="ignition" value="true" />

<datum name="lifeToDateDistanceDisplayValue" value="0000000.0 MI" />

<datum name="ptoFuel" value="0" />

<datum name="type" value="vehicleServiceInfo" />

<datum name="isMoving" value="false" />

<datum name="currentDriver" value="" />

<datum name="returnAirSensor" value="00000000000000" />

<datum name="lifeToDateDistance" value="0000000.0" />

<datum name="inMotionUIEnabled" value="true" />

<datum name="odometer" value="0000000.0" />

<datum name="totalFuel" value="0" />

<datum name="parkedIdleFuel" value="0" />

<datum name="trailerID" value="" />

</data>

</T.6.03.0>

<T.6.03.0>

<eventTS>2014-09-11T00:09:57.0Z</eventTS>

<equipment unitAddress="105328605" equipType="tractor" alias="0105328605" ID="0105328605" />

<position lat="42.76819444444445" lon="-78.59981481481482" posTS="2014-09-11T00:09:52.0Z" />

<posType>1</posType>

<ignitionStatus>1</ignitionStatus>

<tripStatus>O</tripStatus>

<ltdDistance>0.0</ltdDistance>

<speed>0</speed>

<heading>0</heading>

<workflowEventType>unloadingDelay</workflowEventType>

<data id="X_XMM_FILTERED">

<datum name="destTZOffset" value="-04:00" />

<datum name="msgType" value="unloadingDelay" />

<data id="48612FD08700000000000000D118">

<datum name="delayReason" value="Customer Not Ready" />

<datum name="type" value="delayTaskType" />

</data>

<data id="48612FD09700000000000000D119">

<datum name="ignition" value="true" />

<datum name="lifeToDateDistanceDisplayValue" value="0000000.0 MI" />

<datum name="ptoFuel" value="0" />

<datum name="type" value="vehicleServiceInfo" />

<datum name="isMoving" value="false" />

<datum name="currentDriver" value="" />

<datum name="returnAirSensor" value="00000000000000" />

<datum name="lifeToDateDistance" value="0000000.0" />

<datum name="inMotionUIEnabled" value="true" />

<datum name="odometer" value="0000000.0" />

<datum name="totalFuel" value="0" />

<datum name="parkedIdleFuel" value="0" />

<datum name="trailerID" value="" />

</data>

</T.6.03.0>

<T.6.03.0>

<eventTS>2014-09-11T00:10:06.0Z</eventTS>

<equipment unitAddress="105328605" equipType="tractor" alias="0105328605" ID="0105328605" />

<position lat="42.768240740740744" lon="-78.59986111111111" posTS="2014-09-11T00:10:01.0Z" />

<posType>1</posType>

<ignitionStatus>1</ignitionStatus>

<tripStatus>O</tripStatus>

<ltdDistance>0.0</ltdDistance>

<speed>0</speed>

<heading>0</heading>

<workflowEventType>otherDelay</workflowEventType>

<data id="X_XMM_FILTERED">

<datum name="destTZOffset" value="-04:00" />

<datum name="msgType" value="otherDelay" />

<data id="48612FF69D00000000000000D127">

<datum name="delayReason" value="Missed Appointment" />

<datum name="type" value="delayTaskType" />

</data>

<data id="48612FF6AD00000000000000D128">

<datum name="ignition" value="true" />

<datum name="lifeToDateDistanceDisplayValue" value="0000000.0 MI" />

<datum name="ptoFuel" value="0" />

<datum name="type" value="vehicleServiceInfo" />

<datum name="isMoving" value="false" />

<datum name="currentDriver" value="" />

<datum name="returnAirSensor" value="00000000000000" />

<datum name="lifeToDateDistance" value="0000000.0" />

<datum name="inMotionUIEnabled" value="true" />

<datum name="odometer" value="0000000.0" />

<datum name="totalFuel" value="0" />

<datum name="parkedIdleFuel" value="0" />

<datum name="trailerID" value="" />

</data>

</T.6.03.0>

<T.6.03.0>

<eventTS>2014-09-11T00:10:17.0Z</eventTS>

<equipment unitAddress="105328605" equipType="tractor" alias="0105328605" ID="0105328605" />

<position lat="42.76819444444445" lon="-78.59986111111111" posTS="2014-09-11T00:10:12.0Z" />

<posType>1</posType>

<ignitionStatus>1</ignitionStatus>

<tripStatus>O</tripStatus>

<ltdDistance>0.0</ltdDistance>

<speed>0</speed>

<heading>0</heading>

<workflowEventType>otherDelay</workflowEventType>

<data id="X_XMM_FILTERED">

<datum name="destTZOffset" value="-04:00" />

<datum name="msgType" value="otherDelay" />

<data id="4861301FEF00000000000000D136">

<datum name="delayReason" value="Other" />

<datum name="type" value="delayTaskType" />

</data>

<data id="4861301FFF00000000000000D137">

<datum name="ignition" value="true" />

<datum name="lifeToDateDistanceDisplayValue" value="0000000.0 MI" />

<datum name="ptoFuel" value="0" />

<datum name="type" value="vehicleServiceInfo" />

<datum name="isMoving" value="false" />

<datum name="currentDriver" value="" />

<datum name="returnAirSensor" value="00000000000000" />

<datum name="lifeToDateDistance" value="0000000.0" />

<datum name="inMotionUIEnabled" value="true" />

<datum name="odometer" value="0000000.0" />

<datum name="totalFuel" value="0" />

<datum name="parkedIdleFuel" value="0" />

<datum name="trailerID" value="" />

</data>

</T.6.03.0>

<T.6.03.0>

<eventTS>2014-09-11T00:10:30.0Z</eventTS>

<equipment unitAddress="105328605" equipType="tractor" alias="0105328605" ID="0105328605" />

<position lat="42.768240740740744" lon="-78.59981481481482" posTS="2014-09-11T00:10:25.0Z" />

<posType>1</posType>

<ignitionStatus>1</ignitionStatus>

<tripStatus>O</tripStatus>

<ltdDistance>0.0</ltdDistance>

<speed>0</speed>

<heading>0</heading>

<workflowEventType>endDelay</workflowEventType>

<data id="X_XMM_FILTERED">

<datum name="destTZOffset" value="-04:00" />

<datum name="msgType" value="endDelay" />

<data id="48613051FC00000000000000D145">

<datum name="delayReason" value="Early" />

<datum name="type" value="delayTaskType" />

</data>

<data id="486130522B00000000000000D146">

<datum name="ignition" value="true" />

<datum name="lifeToDateDistanceDisplayValue" value="0000000.0 MI" />

<datum name="ptoFuel" value="0" />

<datum name="type" value="vehicleServiceInfo" />

<datum name="isMoving" value="false" />

<datum name="currentDriver" value="" />

<datum name="returnAirSensor" value="00000000000000" />

<datum name="lifeToDateDistance" value="0000000.0" />

<datum name="inMotionUIEnabled" value="true" />

<datum name="odometer" value="0000000.0" />

<datum name="totalFuel" value="0" />

<datum name="parkedIdleFuel" value="0" />

<datum name="trailerID" value="" />

</data>

</T.6.03.0>

<T.6.03.0>

<eventTS>2014-09-11T00:10:38.0Z</eventTS>

<equipment unitAddress="105328605" equipType="tractor" alias="0105328605" ID="0105328605" />

<position lat="42.768240740740744" lon="-78.59981481481482" posTS="2014-09-11T00:10:33.0Z" />

<posType>1</posType>

<ignitionStatus>1</ignitionStatus>

<tripStatus>O</tripStatus>

<ltdDistance>0.0</ltdDistance>

<speed>0</speed>

<heading>0</heading>

<workflowEventType>startEndEvent</workflowEventType>

<data id="X_XMM_FILTERED">

<datum name="destTZOffset" value="-04:00" />

<datum name="msgType" value="startEndEvent" />

<data id="48613073EB00000000000000D148">

<datum name="mins" value="" />

<datum name="cityState" value="" />

<datum name="reason" value="" />

<datum name="gallons" value="" />

<datum name="type" value="startEndEventTaskType" />

<datum name="hrs" value="" />

<datum name="startEndEventReason" value="Start PRE Trip" />

</data>

<data id="48613073EB00000000000000D149">

<datum name="ignition" value="true" />

<datum name="lifeToDateDistanceDisplayValue" value="0000000.0 MI" />

<datum name="ptoFuel" value="0" />

<datum name="type" value="vehicleServiceInfo" />

<datum name="isMoving" value="false" />

<datum name="currentDriver" value="" />

<datum name="returnAirSensor" value="00000000000000" />

<datum name="lifeToDateDistance" value="0000000.0" />

<datum name="inMotionUIEnabled" value="true" />

<datum name="odometer" value="0000000.0" />

<datum name="totalFuel" value="0" />

<datum name="parkedIdleFuel" value="0" />

<datum name="trailerID" value="" />

</data>

</T.6.03.0>

<T.6.03.0>

<eventTS>2014-09-11T00:11:13.0Z</eventTS>

<equipment unitAddress="105328605" equipType="tractor" alias="0105328605" ID="0105328605" />

<position lat="42.768240740740744" lon="-78.59981481481482" posTS="2014-09-11T00:11:08.0Z" />

<posType>1</posType>

<ignitionStatus>1</ignitionStatus>

<tripStatus>O</tripStatus>

<ltdDistance>0.0</ltdDistance>

<speed>0</speed>

<heading>0</heading>

<workflowEventType>startEndEvent</workflowEventType>

<data id="X_XMM_FILTERED">

<datum name="destTZOffset" value="-04:00" />

<datum name="msgType" value="startEndEvent" />

<data id="486130FBE800000000000000D151">

<datum name="mins" value="" />

<datum name="cityState" value="Cleveland OH" />

<datum name="reason" value="" />

<datum name="gallons" value="1000 " />

<datum name="type" value="startEndEventTaskType" />

<datum name="hrs" value="" />

<datum name="startEndEventReason" value="End Fuel" />

</data>

<data id="486130FBF800000000000000D152">

<datum name="ignition" value="true" />

<datum name="lifeToDateDistanceDisplayValue" value="0000000.0 MI" />

<datum name="ptoFuel" value="0" />

<datum name="type" value="vehicleServiceInfo" />

<datum name="isMoving" value="false" />

<datum name="currentDriver" value="" />

<datum name="returnAirSensor" value="00000000000000" />

<datum name="lifeToDateDistance" value="0000000.0" />

<datum name="inMotionUIEnabled" value="true" />

<datum name="odometer" value="0000000.0" />

<datum name="totalFuel" value="0" />

<datum name="parkedIdleFuel" value="0" />

<datum name="trailerID" value="" />

</data>

</T.6.03.0>

<T.6.03.0>

<eventTS>2014-09-11T00:11:34.0Z</eventTS>

<equipment unitAddress="105328605" equipType="tractor" alias="0105328605" ID="0105328605" />

<position lat="42.768240740740744" lon="-78.59981481481482" posTS="2014-09-11T00:11:29.0Z" />

<posType>1</posType>

<ignitionStatus>1</ignitionStatus>

<tripStatus>O</tripStatus>

<ltdDistance>0.0</ltdDistance>

<speed>0</speed>

<heading>0</heading>

<workflowEventType>startEndEvent</workflowEventType>

<data id="X_XMM_FILTERED">

<datum name="destTZOffset" value="-04:00" />

<datum name="msgType" value="startEndEvent" />

<data id="4861314CC700000000000000D154">

<datum name="mins" value="15 " />

<datum name="cityState" value="" />

<datum name="reason" value="Test" />

<datum name="gallons" value="" />

<datum name="type" value="startEndEventTaskType" />

<datum name="hrs" value="8 " />

<datum name="startEndEventReason" value="Wait Time" />

</data>

<data id="4861314CD700000000000000D155">

<datum name="ignition" value="true" />

<datum name="lifeToDateDistanceDisplayValue" value="0000000.0 MI" />

<datum name="ptoFuel" value="0" />

<datum name="type" value="vehicleServiceInfo" />

<datum name="isMoving" value="false" />

<datum name="currentDriver" value="" />

<datum name="returnAirSensor" value="00000000000000" />

<datum name="lifeToDateDistance" value="0000000.0" />

<datum name="inMotionUIEnabled" value="true" />

<datum name="odometer" value="0000000.0" />

<datum name="totalFuel" value="0" />

<datum name="parkedIdleFuel" value="0" />

<datum name="trailerID" value="" />

</data>

</T.6.03.0>

</EssRoot>';

;WITH LEVEL_ZERO AS

(

SELECT

ROW_NUMBER() OVER

(

ORDER BY (SELECT NULL)

) AS NODE_ID

,0 AS NODE_LEVEL

,ROOT.DATA.value('local-name(.)','NVARCHAR(50)') AS NODE_NAME

,ROOT.DATA.query('(*)') AS ANCHOR

FROM @TXML.nodes('*') AS ROOT(DATA)

)

,LEVEL_ONE AS

(

SELECT

L0.NODE_ID AS ROOT_ID

,L0.NODE_LEVEL AS ROOT_LEVEL

,l0.NODE_NAME AS ROOT_NAME

,NEXT_NODE.DATA.value('local-name(.)','NVARCHAR(50)') AS L1_NODE_NAME

,NEXT_NODE.DATA.query('(*)') AS ANCHOR

FROM LEVEL_ZERO L0

OUTER APPLY L0.ANCHOR.nodes('*') AS NEXT_NODE(DATA)

)

,LEVEL_TWO AS

(

SELECT

L1.ROOT_ID

,L1.ROOT_LEVEL

,L1.ROOT_NAME

,L1.L1_NODE_NAME

,NEXT_NODE.DATA.value('local-name(.)','NVARCHAR(50)') AS L2_NODE_NAME

,NEXT_NODE.DATA.query('(*)') AS ANCHOR

FROM LEVEL_ONE L1

OUTER APPLY L1.ANCHOR.nodes('*') AS NEXT_NODE(DATA)

)

,LEVEL_THREE AS

(

SELECT

L2.ROOT_ID

,L2.ROOT_LEVEL

,L2.ROOT_NAME

,L2.L1_NODE_NAME

,L2.L2_NODE_NAME

,NEXT_NODE.DATA.value('local-name(.)','NVARCHAR(50)') AS L3_NODE_NAME

,NEXT_NODE.DATA.query('(*)') AS ANCHOR

FROM LEVEL_TWO L2

OUTER APPLY L2.ANCHOR.nodes('*') AS NEXT_NODE(DATA)

)

,LEVEL_FOUR AS

(

SELECT

L3.ROOT_ID

,L3.ROOT_LEVEL

,L3.ROOT_NAME

,L3.L1_NODE_NAME

,L3.L2_NODE_NAME

,L3.L3_NODE_NAME

,NEXT_NODE.DATA.value('local-name(.)','NVARCHAR(50)') AS L4_NODE_NAME

,NEXT_NODE.DATA.query('(*)') AS ANCHOR

FROM LEVEL_THREE L3

OUTER APPLY L3.ANCHOR.nodes('*') AS NEXT_NODE(DATA)

)

,LEVEL_FIVE AS

(

SELECT

L4.ROOT_ID

,L4.ROOT_LEVEL

,L4.ROOT_NAME

,L4.L1_NODE_NAME

,L4.L2_NODE_NAME

,L4.L3_NODE_NAME

,L4.L4_NODE_NAME

,NEXT_NODE.DATA.value('local-name(.)','NVARCHAR(50)') AS L5_NODE_NAME

,NEXT_NODE.DATA.query('(*)') AS ANCHOR

FROM LEVEL_FOUR L4

OUTER APPLY L4.ANCHOR.nodes('*') AS NEXT_NODE(DATA)

)

SELECT DISTINCT

L5.ROOT_ID

,L5.ROOT_LEVEL

,L5.ROOT_NAME

,L5.L1_NODE_NAME

,L5.L2_NODE_NAME

,L5.L3_NODE_NAME

,L5.L4_NODE_NAME

,NEXT_NODE.DATA.value('local-name(.)','NVARCHAR(50)') AS L6_NODE_NAME

--,NEXT_NODE.DATA.query('(*)') AS ANCHOR

FROM LEVEL_FIVE L5

OUTER APPLY L5.ANCHOR.nodes('*') AS NEXT_NODE(DATA);

Results

ROOT_ID ROOT_LEVEL ROOT_NAME L1_NODE_NAME L2_NODE_NAME L3_NODE_NAME L4_NODE_NAME L6_NODE_NAME

-------- ----------- ---------- ------------- ------------------ ------------- ------------- -------------

1 0 EssRoot T.6.03.0 data data datum NULL

1 0 EssRoot T.6.03.0 data data data

1 0 EssRoot T.6.03.0 workflowEventType NULL NULL

1 0 EssRoot T.6.03.0 ltdDistance NULL NULL

1 0 EssRoot T.6.03.0 posType NULL NULL

1 0 EssRoot T.6.03.0 eventTS NULL NULL

1 0 EssRoot T.6.03.0 speed NULL NULL

1 0 EssRoot T.6.03.0 equipment NULL NULL

1 0 EssRoot T.6.03.0 tripStatus NULL NULL

1 0 EssRoot T.6.03.0 data datum NULL

1 0 EssRoot T.6.03.0 position NULL NULL

1 0 EssRoot T.6.03.0 ignitionStatus NULL NULL

1 0 EssRoot T.6.03.0 heading NULL NULL

1 0 EssRoot T.6.03.0 data data data datum