Import XML data

• MonCircle2006

  Old Hand

  Points: 374

  More actions

  March 9, 2010 at 3:10 pm

  #145701

  Hi everyone,

  I need help trying to import XML data into a relational format. I am able to import the data using a datatype of XML into a table but not sure how to select from the table and parse out the data. I was expecting the xml file to be like :

  <Orders>

  <field name="ORDER_ID">

  <field name="ORDERER_ID">

  <field name="TITLEID">

  <field name="QUANTITYORDERED">

  <field name="DATETAKEN">

  </Orders>

  but instead it's like

  <field name="ORDER_ID">

  <number>1406206.0</number>

  <number>1406207.0</number>

  <number>1406210.0</number>

  <number>1406208.0</number>

  <number>1406209.0</number>

  <field name="TITLEID">

  <number>345.0</number>

  <number>345.0</number>

  <number>338.0</number>

  <number>138.0</number>

  <number>314.0</number>

  Here is a sample of the file I am working with:

  <wddxPacket version="1.0">

  <header />

  <data>

  <recordset rowCount="5" fieldNames="ORDER_ID,TITLEID,QUANTITYORDERED,DATETAKEN" type="coldfusion.sql.QueryTable">

  <field name="ORDER_ID">

  <number>1406206.0</number>

  <number>1406207.0</number>

  <number>1406210.0</number>

  <number>1406208.0</number>

  <number>1406209.0</number>

  <field name="TITLEID">

  <number>345.0</number>

  <number>345.0</number>

  <number>338.0</number>

  <number>138.0</number>

  <number>314.0</number>

  <field name="QUANTITYORDERED">

  <number>1.0</number>

  <number>1.0</number>

  <number>1.0</number>

  <number>1.0</number>

  <number>1.0</number>

  <field name="DATETAKEN">

  <string>03/02/2010 23:21:56</string>

  <string>03/02/2010 23:46:31</string>

  <string>03/03/2010 00:34:16</string>

  <string>03/03/2010 00:34:16</string>

  <string>03/03/2010 00:34:16</string>

  </recordset>

  </data>

  </wddxPacket>

  Please help...it would be greatly appreciated.

  Thanks,

  M

• LutzM

  SSC Guru

  Points: 107049

  More actions

  March 9, 2010 at 4:07 pm

  #1131045

  Doesn't look like a valid xml to me... Where are the closing tags for the various "field name" tags? And what would be your expected result?

  Btw: whoever came up with that xml format: take away whatever kind of drug that person use.... 😉

  ---

  
  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]