how to import XML of this schema into sql server 2005

• qinyan_zhou

  SSC Enthusiast

  Points: 118

  More actions

  February 18, 2009 at 10:11 am

  #205750

  I have a xml file with the following structure, i tried the following script but i got 0 record loaded. XMLImportTest table is fine, its xml_data column has the correct info.

  Does anyone know how to import xml with this type of format? Thanks!

  --------------------------------------------------

  -

  -

  -

  -

  -------------------------------------------------------

  Here is my code:

  CREATE TABLE [dbo].[inbound](

  [EnrolleeID] [nvarchar](255) NULL,

  [SubscriberID] [nvarchar](255) NULL,

  [DependentID] [nvarchar](255) NULL,

  [ProgramID] [nvarchar](255) NULL,

  [ProgramCode] [nvarchar](255) NULL,

  [ProgramStatusCode] [nvarchar](255) NULL,

  [ProgramReasonCode] [nvarchar](255) NULL,

  [ProgramCreateDate] [nvarchar](255) NULL,

  [ProgramEnrolledDate] [nvarchar](255) NULL,

  [ProgramDischargeDate] [nvarchar](255) NULL,

  [ProgramReferralDate] [nvarchar](255) NULL,

  [ReferredByCode] [nvarchar](255) NULL,

  [SystemSource] [nvarchar](255) NULL,

  [ProgramCustom1Text] [nvarchar](255) NULL,

  [StaffID] [nvarchar](255) NULL

  -- [OnetCode] [nvarchar](255) NULL

  ) ON [PRIMARY]

  GO

  CREATE TABLE XmlImportTest

  (

  xmlFileName VARCHAR(300),

  xml_data xml

  )

  GO

  DECLARE @xmlFileName VARCHAR(300)

  SELECT @xmlFileName = 'c:\inbound.xml'

  -- dynamic sql is just so we can use @xmlFileName variable in OPENROWSET

  EXEC('

  INSERT INTO XmlImportTest(xmlFileName, xml_data)

  SELECT ''' + @xmlFileName + ''', xmlData

  FROM

  (

  SELECT *

  FROM OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA

  ) AS FileImport (XMLDATA)

  ')

  GO

  --only one row should be returned

  SELECT * FROM XmlImportTest

  INSERT INTO [dbo].[inbound](

  [EnrolleeID],

  [SubscriberID] ,

  [DependentID] ,

  [ProgramID],

  [ProgramCode] ,

  [ProgramStatusCode] ,

  [ProgramReasonCode] ,

  [ProgramCreateDate] ,

  [ProgramEnrolledDate] ,

  [ProgramDischargeDate] ,

  [ProgramReferralDate] ,

  [ReferredByCode] ,

  [SystemSource] ,

  [ProgramCustom1Text] ,

  [StaffID]

  )

  SELECT tab.col.value('./EnrolleeID[1]','nvarchar(255)') AS 'EnrolleeID'

  ,tab.col.value('./SubscriberID[1]','INT') AS 'SubscriberID'

  ,tab.col.value('./DependentID[1]','INT') AS 'ProgramID'

  ,tab.col.value('./ProgramID[1]','nvarchar(255)') AS 'ProgramID'

  ,tab.col.value('./ProgramCode[1]','nvarchar(max)') AS 'ProgramCode'

  ,tab.col.value('./ProgramStatusCode[1]','nvarchar(255)') AS 'ProgramStatusCode'

  ,tab.col.value('./ProgramReasonCode[1]','nvarchar(255)') AS 'ProgramReasonCode'

  ,tab.col.value('./ProgramCreateDate[1]','INT') AS 'ProgramCreateDate'

  ,tab.col.value('./ProgramEnrolledDate[1]','nvarchar(255)') AS 'ProgramEnrolledDate'

  ,tab.col.value('./ProgramDischargeDate[1]','varchar(255)') AS 'ProgramDischargeDate'

  ,tab.col.value('./ProgramReferralDate[1]','nvarchar(255)') AS 'ProgramReferralDate'

  ,tab.col.value('./ReferredByCode[1]','nvarchar(255)') AS 'ReferredByCode'

  ,tab.col.value('./SystemSource[1]','DATETIME') AS 'SystemSource'

  ,tab.col.value('./ProgramCustom1Text[1]','DATETIME') AS 'ProgramCustom1Text'

  ,tab.col.value('./StaffID[1]','nvarchar(255)') AS 'StaffID'

  FROM [XmlImportTest]

  CROSS APPLY

  xml_data.nodes('//row') AS tab(col)

  GO

  select top *

  from inbound

• qinyan_zhou

  SSC Enthusiast

  Points: 118

  More actions

  February 18, 2009 at 10:18 am

  #946108

  don't know why the xml can't be pasted here...