Export XML file into a table

• Shurkadze

  SSC Eights!

  Points: 942

  More actions

  November 29, 2010 at 1:02 pm

  #229188

  Please help to export the following file into a table.

  Thanks.

  <?xml version="1.0"?>

  <typelistextension

  xmlns="http://guidewire.com/typelists"

  desc="User-defined categories for costs in financial transactions"

  name="CostCategory">

  <typecode

  xmlns=""

  code="addnl_living_expenses"

  desc="Additional Living Expenses"

  name="Additional Living Expenses"

  retired="true">

  <category

  code="claimcost"

  typelist="costtype"/>

  <category

  code="unspecified"

  typelist="costtype"/>

  <category

  code="DWL"

  typelist="coveragetype"/>

  <category

  code="ERTHQK"

  typelist="coveragetype"/>

  <category

  code="FLD"

  typelist="coveragetype"/>

  <category

  code="LU"

  typelist="coveragetype"/>

  <category

  code="MLD"

  typelist="coveragetype"/>

  <typecodelocalization

  xmlns="http://guidewire.com/typelists"

  desc="Additional Living Expenses"

  locale="en_GB"

  name="Additional Living Expenses"/>

  </typecode>

  <typecode

  xmlns=""

  code="autoglass"

  desc="Glass"

  name="Glass"

  retired="true">

  <category

  code="claimcost"

  typelist="costtype"/>

  <category

  code="unspecified"

  typelist="costtype"/>

  <category

  code="ACCTREC"

  typelist="coveragetype"/>

  <category

  code="xs_pr_veh"

  typelist="CoverageSubtype"/>

  <category

  code="trav_pr_veh"

  typelist="CoverageSubtype"/>

  <category

  code="gt_pr_veh"

  typelist="CoverageSubtype"/>

  <category

  code="pll_pr_veh"

  typelist="CoverageSubtype"/>

  <category

  code="other_veh_dam"

  typelist="CoverageSubtype"/>

  <category

  code="terr_pr_veh"

  typelist="CoverageSubtype"/>

  </typecode>

  <typefilter

  desc="CostCategories that are used for glass claims"

  name="glass">

  <include

  code="A30"/>

  <include

  code="A31"/>

  </typefilter>

  </typelistextension>

• Shurkadze

  SSC Eights!

  Points: 942

  More actions

  November 29, 2010 at 5:48 pm

  #1256552

  Does the file need to be imported as 1 column in 1 row and then parced out or

  is there a way to import the file parsed out into separate columns/ rows for a newly created table?

  I am totally new to this, so

  thanks in advance for any help.

• LutzM

  SSC Guru

  Points: 107049

  More actions

  November 30, 2010 at 2:18 am

  #1256647

  In general, both options you described can be used.

  However, the xml sample you provided cannot be shredded into separate columns.

  The reason is the multiple declaration of the default namespace. (xmlns="http://guidewire.com/typelists" -> xmlns="" -> xmlns="http://guidewire.com/typelists" -> xmlns="").

  You'd einther need to define separate namespaces or remove the additional declaration. Assuming the latter and that your xml data is stored in the variable @xml you could split your data using the following approach:

  ;WITH XMLNAMESPACES (DEFAULT 'http://guidewire.com/typelists')

  SELECT

  v.value('@code[1]','varchar(30)') as typecode,

  v.value('@desc[1]','varchar(30)') as typedesc,

  v.value('@name[1]','varchar(30)') as tpyename,

  c.value('@code[1]','varchar(30)') as categorycode

  FROM @xml.nodes('typelistextension/typecode') U(v)

  CROSS APPLY U.v.nodes('category') T(c)

  If we'd know the target structure you'Re trying to populate we'd probably be able to help you even further.

  ---

  
  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]