XML Help

• Mark Taylor-212571

  Ten Centuries

  Points: 1196

  More actions

  June 20, 2007 at 2:17 am

  #73203

  All I'm new to XML and I'm looking for some help.

  I have received a file from the British Government in an XML format and I have not got the first idea how to read this file into an SQL Server 2000 database or even if it possible.  Can any one give guidance on any resources (Online, book) that I can use to learn how to handle this file.

  Thank you in advance

• shrikant-279400

  SSCommitted

  Points: 1651

  More actions

  June 20, 2007 at 3:38 am

  #714042

  you should use OPENXML, which provides rowset over XML-document which is similar to view or table. checkout following example

  DECLARE @idoc int

  DECLARE @doc varchar(1000)

  SET @doc ='

  <ROOT>

  <Customer CustomerID="VINET" ContactName="Paul Henriot">

  </Customer>

  <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">

  </Customer>

  </ROOT>'

  --create internal presentation of XML doc

  EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

   

  SELECT    *

  FROM OPENXML (@idoc, '/ROOT/Customer',1)

       WITH (CustomerID  varchar(10),

             ContactName varchar(20))

  --remove internal presentation of XML doc

  EXEC sp_xml_removedocument @idoc

  how does OPENXML works? checkout http://msdn2.microsoft.com/en-us/library/ms175160(SQL.90).aspx for more details.  

    

  Regards
  Shrikant Kulkarni

• bledu

  SSChampion

  Points: 12335

  More actions

  June 21, 2007 at 12:53 am

  #714266

  in addition to that check on the responsible authorities website e.g. hmrc , where they will most likely give you the xml file definition file. though they might have sent it to you already

  ---

  Everything you can imagine is real.