XML Shredding / parsing

Question

XML Shredding / parsing

Jeffery Williams

SSCertifiable

Points: 6441
More actions
September 13, 2014 at 3:12 pm

#290074

Ok I am faced with working with XML on a regular basis, which is fine. So I am presently having to manually code lines such as this
DECLARE @ViewSN INT
IF NOT EXISTS (select null from tblviews where viewcode = 'loadAtTerm') --<workflowEventType>loadAtTerminal</workflowEventType>
insert into tblviews (ViewName,Description,OutBoundForm,StoredProcSN,TriggersReply,ViewCode,DispXactLayer,DispXactViewType,DispXfcTag,Comments)
select 'QC:WF-LoadAtTerminal','This View Corresponds to the XML for loadAtTerminal in Omnitracs Workflow','0',NULL,'0', 'loadAtTerm','MCOM','MCOM',NULL,NULL
SELECT @ViewSN = sn from tblviews where viewcode = 'loadAtTerm'
after inserting the NODE in the above table and returning my ID I then insert the fields in another table like this:
IF NOT EXISTS (select null from tblviewfields where ViewNumber = @Viewsn and fieldname = 'planId')
INSERT INTO tblviewfields (ViewNumber,FieldName,IsRepeating,FieldNumber,FileNumber,Required,BusinessRule,VerifyFile,VerifyJoinField,TTSFieldName,SQLTableName,SQLFieldName,DefaultLength,DefaultType,DisplayedFieldName,DispXfcTag,BusinessRuleType,Comments,DefaultPrefix,DefaultSuffix,[Default])
SELECT @ViewSN,'planId',0,0,0,0,NULL,0,0,NULL,'','',20,1,'planId',NULL,NULL,NULL,NULL,NULL,''
IF NOT EXISTS (select null from tblviewfields where ViewNumber = @Viewsn and fieldname = 'destSortId')
INSERT INTO tblviewfields (ViewNumber,FieldName,IsRepeating,FieldNumber,FileNumber,Required,BusinessRule,VerifyFile,VerifyJoinField,TTSFieldName,SQLTableName,SQLFieldName,DefaultLength,DefaultType,DisplayedFieldName,DispXfcTag,BusinessRuleType,Comments,DefaultPrefix,DefaultSuffix,[Default])
SELECT @ViewSN,'destSortId',0,0,0,0,NULL,0,0,NULL,'','',1,49,'destSortId',NULL,NULL,NULL,NULL,NULL,''
and so on....
What would be really useful is to be able to present any xml file and automatically parse the NODE names into a memory variable table and then the fields of each node in another.
Can anyone advice on how to accomplish this? I searched for scripts and topics and have not really found anything that will do this.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

Quick thought, you may want to take a look at Jacob Sebastian's XMLTable function. Also if you have an XSD for the XML, you can create a schema collection and interrogate the system view for it's properties.

😎

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 2

I don't have any xsd and im not sure that is available to me. That said I will have a look at the script, thank you!

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 3

I created an XSD (I hope) Is there anywhere you might be able to point me in using this to automatically create tables in SQL that match the XML schema?

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 4

Quick thought, do you have an example of the XML, depending on the complexity and structure one might find something that fits the purpose.

😎

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 5

Sure thing duh! LOL..

Sorry guys.

https://www.dropbox.com/s/1nr8j939esk7p3g/GetMessage_ess__2014-09-10T20_12_55.xml?dl=0

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 6

Here is a quick exsample of node shredding based on your XML sample, if the structure of the XML is not changing then this could be the right direction.

😎

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

SELECT

DENSE_RANK() OVER

(

ORDER BY T_6_03_0.DATA.value('eventTS[1]' ,'DATETIME')

) AS EVENT_RID

,T_6_03_0.DATA.value('eventTS[1]' ,'DATETIME') AS eventTS

,EQUIPMENT.DATA.value('@unitAddress' ,'BIGINT') AS EQPM_unitAddress

,EQUIPMENT.DATA.value('@equipType' ,'VARCHAR(25)') AS EQPM_equipType

,EQUIPMENT.DATA.value('@alias' ,'VARCHAR(25)') AS EQPM_alias

,EQUIPMENT.DATA.value('@ID' ,'VARCHAR(25)') AS EQPM_ID

,POSITION.DATA.value('@lat' ,'FLOAT') AS POS_lat

,POSITION.DATA.value('@lon' ,'FLOAT') AS POS_lon

,POSITION.DATA.value('@posTS' ,'DATETIME') AS POS_posTS

,T_6_03_0.DATA.value('posType[1]' ,'INT') AS posType

,T_6_03_0.DATA.value('ignitionStatus[1]' ,'INT') AS ignitionStatus

,T_6_03_0.DATA.value('tripStatus[1]' ,'CHAR(1)') AS tripStatus

,T_6_03_0.DATA.value('ltdDistance[1]' ,'FLOAT') AS ltdDistance

,T_6_03_0.DATA.value('speed[1]' ,'FLOAT') AS speed

,T_6_03_0.DATA.value('heading[1]' ,'FLOAT') AS heading

,T_6_03_0.DATA.value('workflowEventType[1]' ,'VARCHAR(25)') AS workflowEventType

FROM @TXML.nodes('EssRoot/T.6.03.0') AS T_6_03_0(DATA)

OUTER APPLY T_6_03_0.DATA.nodes('equipment') AS EQUIPMENT(DATA)

OUTER APPLY T_6_03_0.DATA.nodes('position') AS POSITION(DATA);

SELECT

DENSE_RANK() OVER

(

ORDER BY T_6_03_0.DATA.value('eventTS[1]' ,'DATETIME')

) AS EVENT_RID

,ROW_NUMBER() OVER

(

PARTITION BY T_6_03_0.DATA.value('eventTS[1]' ,'DATETIME')

ORDER BY (SELECT NULL)

) AS T001_RID

,T_6_03_0.DATA.value('eventTS[1]' ,'DATETIME') AS eventTS

,T001.DATA.value('@id','NVARCHAR(24)')

,T002.DATA.value('@name','NVARCHAR(24)')

,T002.DATA.value('@value','NVARCHAR(24)')

FROM @TXML.nodes('EssRoot/T.6.03.0') AS T_6_03_0(DATA)

OUTER APPLY T_6_03_0.DATA.nodes('data') AS T001(DATA)

OUTER APPLY T001.DATA.nodes('datum') AS T002(DATA);

SELECT

DENSE_RANK() OVER

(

ORDER BY T_6_03_0.DATA.value('eventTS[1]' ,'DATETIME')

) AS EVENT_RID

,ROW_NUMBER() OVER

(

PARTITION BY T_6_03_0.DATA.value('eventTS[1]' ,'DATETIME')

ORDER BY (SELECT NULL)

) AS T001_RID

,T_6_03_0.DATA.value('eventTS[1]' ,'DATETIME') AS eventTS

,T001.DATA.value('@id','NVARCHAR(24)')

,D002.DATA.value('@id','NVARCHAR(34)')

FROM @TXML.nodes('EssRoot/T.6.03.0') AS T_6_03_0(DATA)

OUTER APPLY T_6_03_0.DATA.nodes('data') AS T001(DATA)

OUTER APPLY T001.DATA.nodes('data') AS D002(DATA);

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 7

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