Parsing xml data in SQL

Having never used SQL for anything serious I have been asked to look at importing multiple data files into a database and have come to a dead stop. The XML file is a repeating structure with but the number of <SampleVial>s and the number of <Particles>s in each file is a variable...example shown below

<AcquisitionIntervals>
  <SampleVial id="1">
    <AcquisitionInterval>
      <StartOfIntervalUpdate>
        <Id>0</Id>
        <ElapsedTime>0.354070258140564</ElapsedTime>
        <VialIndex>0</VialIndex>
        <CycleIndex>0</CycleIndex>
        <Mass>-1</Mass>
        <TotalNumberOfParticlesThroughSensor>0</TotalNumberOfParticlesThroughSensor>
        <TotalVolumeThroughSensor>0</TotalVolumeThroughSensor>
      </StartOfIntervalUpdate>
      <Particles>
        <AcquisitionUpdate>
          <Id>1</Id>
          <ElapsedTime>0.357820471127828</ElapsedTime>
          <VialIndex>0</VialIndex>
          <CycleIndex>0</CycleIndex>
          <Mass>44.407905648898</Mass>
          <TotalNumberOfParticlesThroughSensor>1</TotalNumberOfParticlesThroughSensor>
          <TotalVolumeThroughSensor>0.000151105559071708</TotalVolumeThroughSensor>
        </AcquisitionUpdate>
        <AcquisitionUpdate>
          <Id>2</Id>
          <ElapsedTime>0.357820471127828</ElapsedTime>
          <VialIndex>0</VialIndex>
          <CycleIndex>0</CycleIndex>
          <Mass>62.5123878057821</Mass>
          <TotalNumberOfParticlesThroughSensor>2</TotalNumberOfParticlesThroughSensor>
          <TotalVolumeThroughSensor>0.000151105559071708</TotalVolumeThroughSensor>
        </AcquisitionUpdate>
   </Particles>
      <EndOfIntervalUpdate>
        <Id>3</Id>
        <ElapsedTime>1.0134746392568</ElapsedTime>
        <VialIndex>0</VialIndex>
        <CycleIndex>0</CycleIndex>
        <Mass>-2</Mass>
        <TotalNumberOfParticlesThroughSensor>304</TotalNumberOfParticlesThroughSensor>
        <TotalVolumeThroughSensor>0.0425356529565674</TotalVolumeThroughSensor>
      </EndOfIntervalUpdate>
    </AcquisitionInterval>
  </SampleVial>
  <SampleVial id="2">
    <AcquisitionInterval>
      <StartOfIntervalUpdate>

I need to pull all of the particle data associated with each <SampleVial>. If anyone can point me in the right direction I would be very grateful.

Thanks for looking.

Pete

July 29th, 2015 6:36pm

You don't say how you want the values to be extracted, but here is something to work from.

Also, see this section in an article of mine for some more information:
http://www.sommarskog.se/arrays-in-sql-2005.html#XML

DECLARE @x xml = '
<AcquisitionIntervals>
  <SampleVial id="1">
    <AcquisitionInterval>
      <StartOfIntervalUpdate>
        <Id>0</Id>
        <ElapsedTime>0.354070258140564</ElapsedTime>
        <VialIndex>0</VialIndex>
        <CycleIndex>0</CycleIndex>
        <Mass>-1</Mass>
        <TotalNumberOfParticlesThroughSensor>0</TotalNumberOfParticlesThroughSensor>
        <TotalVolumeThroughSensor>0</TotalVolumeThroughSensor>
      </StartOfIntervalUpdate>
      <Particles>
        <AcquisitionUpdate>
          <Id>1</Id>
          <ElapsedTime>0.357820471127828</ElapsedTime>
          <VialIndex>0</VialIndex>
          <CycleIndex>0</CycleIndex>
          <Mass>44.407905648898</Mass>
          <TotalNumberOfParticlesThroughSensor>1</TotalNumberOfParticlesThroughSensor>
          <TotalVolumeThroughSensor>0.000151105559071708</TotalVolumeThroughSensor>
        </AcquisitionUpdate>
        <AcquisitionUpdate>
          <Id>2</Id>
          <ElapsedTime>0.357820471127828</ElapsedTime>
          <VialIndex>0</VialIndex>
          <CycleIndex>0</CycleIndex>
          <Mass>62.5123878057821</Mass>
          <TotalNumberOfParticlesThroughSensor>2</TotalNumberOfParticlesThroughSensor>
          <TotalVolumeThroughSensor>0.000151105559071708</TotalVolumeThroughSensor>
        </AcquisitionUpdate>
   </Particles>
      <EndOfIntervalUpdate>
        <Id>3</Id>
        <ElapsedTime>1.0134746392568</ElapsedTime>
        <VialIndex>0</VialIndex>
        <CycleIndex>0</CycleIndex>
        <Mass>-2</Mass>
        <TotalNumberOfParticlesThroughSensor>304</TotalNumberOfParticlesThroughSensor>
        <TotalVolumeThroughSensor>0.0425356529565674</TotalVolumeThroughSensor>
      </EndOfIntervalUpdate>
    </AcquisitionInterval>
  </SampleVial>
  <SampleVial id="2">
   <AcquisitionInterval>
      <StartOfIntervalUpdate>
        <Id>0</Id>
        <ElapsedTime>0.354070258140564</ElapsedTime>
        <VialIndex>0</VialIndex>
        <CycleIndex>0</CycleIndex>
        <Mass>-1</Mass>
        <TotalNumberOfParticlesThroughSensor>0</TotalNumberOfParticlesThroughSensor>
        <TotalVolumeThroughSensor>0</TotalVolumeThroughSensor>
      </StartOfIntervalUpdate>
      <Particles>
        <AcquisitionUpdate>
          <Id>1</Id>
          <ElapsedTime>0.357820471127828</ElapsedTime>
          <VialIndex>0</VialIndex>
          <CycleIndex>0</CycleIndex>
          <Mass>44.407905648898</Mass>
          <TotalNumberOfParticlesThroughSensor>1</TotalNumberOfParticlesThroughSensor>
          <TotalVolumeThroughSensor>0.000151105559071708</TotalVolumeThroughSensor>
        </AcquisitionUpdate>
        <AcquisitionUpdate>
          <Id>2</Id>
          <ElapsedTime>0.357820471127828</ElapsedTime>
          <VialIndex>0</VialIndex>
          <CycleIndex>0</CycleIndex>
          <Mass>62.5123878057821</Mass>
          <TotalNumberOfParticlesThroughSensor>2</TotalNumberOfParticlesThroughSensor>
          <TotalVolumeThroughSensor>0.000151105559071708</TotalVolumeThroughSensor>
        </AcquisitionUpdate>
   </Particles>
      <EndOfIntervalUpdate>
        <Id>3</Id>
        <ElapsedTime>1.0134746392568</ElapsedTime>
        <VialIndex>0</VialIndex>
        <CycleIndex>0</CycleIndex>
        <Mass>-2</Mass>
        <TotalNumberOfParticlesThroughSensor>304</TotalNumberOfParticlesThroughSensor>
        <TotalVolumeThroughSensor>0.0425356529565674</TotalVolumeThroughSensor>
      </EndOfIntervalUpdate>
    </AcquisitionInterval>
  </SampleVial>
</AcquisitionIntervals>'

SELECT SV.sv.value('@id', 'int') AS SampleVialId,
       SOI.soi.value('(Id/text())[1]', 'int') AS SampleOfUpdateIntervalId,
       SOI.soi.value('(ElapsedTime/text())[1]', 'float') AS ElapsedTime
FROM   @x.nodes('/AcquisitionIntervals/SampleVial') AS SV(sv)
CROSS  APPLY SV.sv.nodes('AcquisitionInterval/StartOfIntervalUpdate') AS SOI(soi)

Free Windows Admin Tool Kit Click here and download it now
July 30th, 2015 5:29pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics