Problem with XML Parsing in SQL Server 2000

• Mahesh Gupta-325917

  SSC Journeyman

  Points: 84

  More actions

  March 26, 2010 at 9:35 am

  #142207

  Hello, I am trying to parse the below XML using OPENXML in SQL Server 2000. It's doing good apart from first 2 columns 'type' and 'perioddate' are not changing their values for different XML node.

  Any help is much appreciated.

  DECLARE @xml_text VARCHAR(8000), @i INT

  SELECT @xml_text = '

  <?xml version="1.0" encoding="UTF-8"?>

  <period>

  <table type="Interest Rate">

  <perioddate>05/02/2001</perioddate>

  <row type="coldesc">

  <cell id="tl" bold="1" cspan="c1">Loan Amount1</cell>

  <cell id="c605" bold="2" abbr=" percentage Annual Percentage Rate" cspan="c2">% APR</cell>

  </row>

  </table>

  <table type="Repayment">

  <perioddate>05/01/2002</perioddate>

  <row type="coldesc1">

  <cell id="tl" bold="3" cspan="c3">Loan Amount2</cell>

  <cell id="c525" bold="4" cspan="c4">1,000</cell>

  </row>

  </table>

  </period>

  '

• Paul White

  SSC Guru

  Points: 150468

  More actions

  March 28, 2010 at 11:39 pm

  #1141185

  DECLARE @xmlDocument VARCHAR(8000),

  @docHandle INTEGER;

  SET @xmlDocument = '

  <?xml version="1.0" encoding="UTF-8"?>

  <period>

  <table type="Interest Rate">

  <perioddate>05/02/2001</perioddate>

  <row type="coldesc">

  <cell id="tl" bold="1" cspan="c1">Loan Amount1</cell>

  <cell id="c605" bold="2" abbr=" percentage Annual Percentage Rate" cspan="c2">% APR</cell>

  </row>

  </table>

  <table type="Repayment">

  <perioddate>05/01/2002</perioddate>

  <row type="coldesc1">

  <cell id="tl" bold="3" cspan="c3">Loan Amount2</cell>

  <cell id="c525" bold="4" cspan="c4">1,000</cell>

  </row>

  </table>

  </period>

  ';

  EXECUTE sys.sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;

  SELECT *

  FROM OPENXML(@docHandle, '/period/table/row/cell', 2)

  WITH (

  table_type VARCHAR(50) '../../@type',

  period_date DATETIME '../../perioddate',

  row_type VARCHAR(50) '../@type',

  cell_id VARCHAR(50) '@id',

  cell_value VARCHAR(50) '.',

  abbr VARCHAR(50) '@abbr',

  bold INTEGER '@bold',

  cspan VARCHAR(50) '@cspan'

  );

  EXECUTE sys.sp_xml_removedocument @docHandle;

  Paul White
  All articles available on SQL.kiwi
  @SQL_Kiwi