sp_xml_insertfromxml

• nratikanta

  Grasshopper

  Points: 13

  More actions

  September 17, 2003 at 2:47 am

  #84435

  I am having a XML file. I want to read the data from that XML file and insert into a new table.

• ckempste

  SSCoach

  Points: 17983

  More actions

  September 17, 2003 at 4:42 am

  #474597

  Hi

  Please dont double post. Makes life harded when tracking your problem/responses.

  Cheers

  Ck

  Chris Kempster

  http://www.chriskempster.com

  Author of "SQL Server 2k for the Oracle DBA"

  
  Chris Kempster
  www.chriskempster.com
  Author of "SQL Server Backup, Recovery & Troubleshooting"
  Author of "SQL Server 2k for the Oracle DBA"

• s_popovski

  SSC Veteran

  Points: 271

  More actions

  September 19, 2003 at 1:02 am

  #474982

  You can use SQLXMLBulkLoad (SQLXML3.0 SP1) in this way

  first you have to create table

  Customer (CustomerId int,CompanyName varchar,City varchar)

  Insertcustomers.vbs -- vb script

  Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad.3.0")

  objBL.ConnectionString = "provider=SQLOLEDB.1;data source=MyServer;database=MyDatabase;uid=sa;pwd=pass"

  objBL.ErrorLogFile = "c:\error.log"

  objBL.Execute "c:\customermapping.xml", "c:\customers.xml"

  Set objBL = Nothing

  customers.xml -- file you want to import

  <ROOT>

  <Customers>

  <CustomerId>1111</CustomerId>

  <CompanyName>Sean Chai</CompanyName>

  <City>NY</City>

  </Customers>

  <Customers>

  <CustomerId>1112</CustomerId>

  <CompanyName>Tom Johnston</CompanyName>

  <City>LA</City>

  </Customers>

  <Customers>

  <CustomerId>1113</CustomerId>

  <CompanyName>Institute of Art</CompanyName>

  </Customers>

  </ROOT>

  Customermapping.xml -- mapping xml elements to table and table fields

  <?xml version="1.0" ?>

  <Schema xmlns="urn:schemas-microsoft-com:xml-data"

  xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"

  xmlns:sql="urn:schemas-microsoft-com:xml-sql" >

  <ElementType name="CustomerId" dt:type="int" />

  <ElementType name="CompanyName" dt:type="string" />

  <ElementType name="City" dt:type="string" />

  <ElementType name="ROOT" sql:is-constant="1">

  <element type="Customers" />

  </ElementType>

  <ElementType name="Customers" sql:relation="Customer">

  <element type="CustomerId" sql:field="CustomerId" />

  <element type="CompanyName" sql:field="CompanyName" />

  <element type="City" sql:field="City" />

  </ElementType>

  </Schema>

  If you want import xml file directly from sp you can use

  sp_OA**** procedures