Extracting data into Table using OpenXML

• rajankjohn

  SSCertifiable

  Points: 5061

  More actions

  August 4, 2010 at 10:47 am

  #222119

  Can someone please help me extracting the below one?

  <Return>

  <SOURCE PROCESSSTATECODE="30" ERRORCODE="4835" FORMNAME="26" FIELDNAME="">

  <DEST MESSAGE_ID="10289">

  <MSG> If some investment is not at risk, you will have to return.

  </MSG>

  <MAPPING>

  <MAP MAP_ID="11375" ENTITY="" FORM="" FIELD="" PAGE="w6l0p1257.aspx" DDM_TYPE="Fallback" OVERRIDE_MSG="" />

  </MAPPING>

  </DEST>

  </SOURCE>

  </Return>

  I tried the following; but I am not able to extract the data which is out of <MAPPING> -

  SELECT * into #t FROM OPENXML( @i, '/Return/SOURCE/DEST/MAPPING/MAP',2)

  WITH (

  Entity VARCHAR(10) '/Return/@ENTITY',

  MapId VARCHAR(10) '@MAP_ID',

  Form VARCHAR(10) '@FORM',

  Field VARCHAR(10) '@FIELD',

  Page VARCHAR(1000) '@PAGE',

  DDMType VARCHAR(10) '@DDM_TYPE',

  OverrideMessage VARCHAR(10) '@OVERRIDE_MSG',

  [Message] VARCHAR(MAX) '/Return/SOURCE/DEST/MSG',

  MessageId VARCHAR(10) '/Return/SOURCE/DEST/@MESSAGE_ID',

  ProcessStateCode VARCHAR(10) '/Return/SOURCE/@PROCESSSTATECODE',

  ErrorCode VARCHAR(10) '/Return/SOURCE/@ERRORCODE',

  FormName VARCHAR(10) '/Return/SOURCE/@FORMNAME',

  FieldName VARCHAR(10) '/Return/SOURCE/@FIELDNAME'

  )

  I am getting the first record value of the second part (starting from MessageId to right) gets repeated; the first part is fine.

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  August 4, 2010 at 5:39 pm

  #1202441

  The problem is that you are using absolute paths rather than relative paths.SELECT *

  FROM OPENXML( @i, '/Return/SOURCE/DEST/MAPPING/MAP',2)

  WITH (

  Entity VARCHAR(10) '@ENTITY',

  MapId VARCHAR(10) '@MAP_ID',

  Form VARCHAR(10) '@FORM',

  Field VARCHAR(10) '@FIELD',

  Page VARCHAR(1000) '@PAGE',

  DDMType VARCHAR(10) '@DDM_TYPE',

  OverrideMessage VARCHAR(10) '@OVERRIDE_MSG',

  [Message] VARCHAR(MAX) '../../MSG',

  MessageId VARCHAR(10) '../../@MESSAGE_ID',

  ProcessStateCode VARCHAR(10) '../../../@PROCESSSTATECODE',

  ErrorCode VARCHAR(10) '../../../@ERRORCODE',

  FormName VARCHAR(10) '../../../@FORMNAME',

  FieldName VARCHAR(10) '../../../@FIELDNAME'

  )

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• rajankjohn

  SSCertifiable

  Points: 5061

  More actions

  August 5, 2010 at 1:12 pm

  #1203082

  Thanks a lot!!!