Uploading to xml

• Muthukkalavalli

  SSC Rookie

  Points: 28

  More actions

  February 16, 2006 at 10:47 am

  #90832

  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.

• rsharma

  SSCarpal Tunnel

  Points: 4865

  More actions

  February 16, 2006 at 11:01 am

  #621432

  Search for OpenXML on this site and you wil get some very good articles on how to do this - infact, there have been some recent articles which talk about this.

   

• Kristipati Subramanyam

  Grasshopper

  Points: 16

  More actions

  February 19, 2006 at 11:28 pm

  #621931

  Hi

  I am Kristipati Subramanyam, I think .. following example will satisfy your needs

  --===============================================================

  -- Let's say the following XML document is saved as c:\books.xml:

  --===============================================================

  <?xml version="1.0"?>

  <Books>

        <Book>

              <Title>XML Application Development with MSXML 4.0</Title>

              <Publisher>Wrox Press</Publisher>

              <DateOfPurchase>2/1/2002</DateOfPurchase>

        </Book>

        <Book>

              <Title>Professional SQL Server 2000 XML</Title>

              <Publisher>Wrox Press</Publisher>

              <DateOfPurchase>9/10/2001</DateOfPurchase>

        </Book>

  </Books>

  --===============================================================

  -- Create Table

  --===============================================================

  IF OBJECT_ID('tblBooks', 'U') IS NOT NULL

   DROP TABLE tblBooks

  GO

  CREATE TABLE tblBooks

   (rowID int IDENTITY,

   Title nvarchar(255),

   Publisher nvarchar(255),

   DateOfPurchase datetime)

  --===============================================================

  -- Use the following Script..

  --===============================================================

  SET NOCOUNT ON

  CREATE TABLE #tmpFileLines (rowID int IDENTITY, lineData nvarchar(255))

  INSERT #tmpFileLines EXEC master.dbo.xp_cmdshell 'TYPE c:\books.xml'

  DECLARE @strXMLText nvarchar(4000)

  SELECT @strXMLText =

   CASE rowID WHEN 1 THEN

    ISNULL(RTRIM(lineData), '')

   ELSE

    @strXMLText + ISNULL(RTRIM(lineData), '')

   END

  FROM #tmpFileLines ORDER BY rowID ASC

  PRINT '-------------------------------'

  PRINT 'Bytes read from the file:'

  PRINT DATALENGTH(@strXMLText)

  PRINT '-------------------------------'

  DROP TABLE #tmpFileLines

  DECLARE @hDoc int

  EXEC sp_xml_preparedocument @hDoc OUTPUT, @strXMLText

  INSERT INTO tblBooks

   SELECT *

   FROM OPENXML(@hDOC, '/Books/Book', 2)

   WITH

   (

    Title nvarchar(255),

    Publisher nvarchar(255),

    DateOfPurchase datetime

    )

  EXEC sp_xml_removedocument @hdoc

  GO

  SELECT * FROM tblBooks

  GO

  SET NOCOUNT OFF