Parsing out XML on the same level

• jbalbo

  SSCertifiable

  Points: 7583

  More actions

  August 20, 2015 at 11:55 am

  #299932

  Hi

  I’m trying to parse out

  <Discharge_x0020_Time>

  <time>

  <Hour>11</Hour>

  <Minute>:00</Minute>

  <AM_x002F_PM>AM</AM_x002F_PM>

  </time>

  </Discharge_x0020_Time>

  Into Hours, minutes, and ampm

  I use

  Select...

  ,DISCHARGEHOUR.value('(./Discharge_x0020_Time/time/Hour)[1]', 'varchar(10)') AS [hour]

  ,DISCHARGEMINUTES.value('(./Discharge_x0020_Time/time/Hour:minute)[1]', 'varchar(10)') AS [Minutes]

  ,DISCHARGEAMPM.value('(./Discharge_x0020_Time/time/Hour/minute/AM_x002F_PM)[1]', 'varchar(10)') AS [ampm]

  FROM ...

  CROSS APPLY data.nodes('/Data') a(DISCHARGEHOUR)

  CROSS APPLY data.nodes('/Data') b(DISCHARGEMINUTES)

  CROSS APPLY data.nodes('/Data') b(DISCHARGEAMPM)

  But minutes AND AMPM come up as NULL I assume I am setting up something wrong with the level on minutes AND AMPM

  Also, can I disregard the ":" in the minutes

  Thanks

  Joe

• jbalbo

  SSCertifiable

  Points: 7583

  More actions

  August 20, 2015 at 12:08 pm

  #1821510

  wow I surprised myself I actually figured it out after I put this up

  I guess see it on a different screen helped...lol

  I just needed the name at that level

  DISCHARGEMINUTES.value('(./Discharge_x0020_Time/time/Minute)[1]', 'varchar(100)') AS [Minutes]

  if anyone know how to eliminate the : that would be greta

  Thanks