sql

• Muthukkalavalli

  SSC Rookie

  Points: 28

  More actions

  February 16, 2006 at 10:50 am

  #167622

  I Need uploading an XML file to SQL Server... The scipt should use MS SQL Server Bulk import capability (OpenXML)...How can i do this.

• February 20, 2006 at 8:00 am

  This was removed by the editor as SPAM

• lichtenberg

  SSCommitted

  Points: 1543

  More actions

  February 21, 2006 at 3:50 am

  #622204

  Try :

  CREATE

  TABLE xml_documents( x XML );

  INSERT

  INTO xml_documents ( x )

  SELECT * FROM OPENROWSET(BULK N'myXmlFile.xml', SINGLE_BLOB) AS x;

  GO

   

  More information on :

  http://blogs.msdn.com/stuartpa/archive/2005/07/19/440572.aspx

   

• alexia-294642

  SSChasing Mays

  Points: 600

  More actions

  February 21, 2006 at 11:31 pm

  #622521

  Try this :

  USE NorthWind

  CREATE PROC GetRegions_XML

   @empdata text

  AS

  begin

  DECLARE @hDoc int

  DECLARE @tbl TABLE(state VARCHAR(20))

  exec sp_xml_preparedocument @hDoc OUTPUT, @empdata  

  INSERT @tbl

  SELECT StateName

  FROM OPENXML(@hDoc, 'root/States')  

   WITH (StateName VARCHAR(20))

  EXEC sp_xml_removedocument @hDoc  

  SELECT * FROM Suppliers

  WHERE Region IN (SELECT * FROM @tbl)

  end

   

  Then, run this script :

  declare @s-2 varchar(100)

  set @s-2 = '<root><States StateName = "LA"/>

    <States StateName = "MI"/>

    <States StateName = "OR"/></root>'

  exec GetRegions_XML @s-2