How to load XML file into Database in SSIS

• hegdesuchi

  Hall of Fame

  Points: 3047

  More actions

  October 7, 2016 at 6:47 am

  #330736

  Hi All,

  My xml File is below struture.

  <patientRequest>

  <invocationPoint>RequestDetails</invocationPoint>

  <requestID>000004</requestID>

  <requestType>XX</requestType>

  <claimNumber />

  <SourceSystemId>Source</SourceSystemId>

  <overallRequestStatus code="XXXXXX">Approved</overallRequestStatus>

  <treatmentSetting code="00">' XXXXXXXXX</treatmentSetting>

  <member>

  <memberID>12345678</memberID>

  </member>

  <facility>

  <Id>1234</Id>

  </patientRequest>

  I am trying to do something like this with Rowxml

  DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

  SELECT @XML = "Above XML"

  EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

  SELECT *

  FROM OPENXML(@hDoc, '/PatientRequest/treatmentSetting',2)

  WITH

  (

  requestID [varchar](500) '../requestID',

  requestType [varchar](100) '../requestType',

  SourceSystemId [varchar](50) '../SourceSystemId',

  overallRequestStatus [varchar](100)'../overallRequestStatus',

  treatmentSetting [varchar](10) '@code'

  )

  EXEC sp_xml_removedocument @hDoc

  I get proper result with this. But If I want member then I am not able to get the value.

  DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

  SELECT @XML = "Above XML"

  EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

  SELECT *

  FROM OPENXML(@hDoc, '/PatientRequest/treatmentSetting/member',2)

  WITH

  (

  requestID [varchar](500) '../../requestID',

  requestType [varchar](100) '../../requestType',

  SourceSystemId [varchar](50) '../../SourceSystemId',

  overallRequestStatus [varchar](100)'../../overallRequestStatus',

  treatmentSetting [varchar](10) '../@code',

  memberID [varchar](10) 'memberID'

  )

  Where am I going wrong? I want all the details in one table.

  Any help is appreciated!!

  Thanks