Can I use both element and attribute centric mapping?

• Kbrown

  SSCommitted

  Points: 1794

  More actions

  August 15, 2006 at 2:15 pm

  #103221

  Hello,

  I'm trying to write the contents of an xml document to a database table.  I'm stuck on how to combine element and attribute centric schema.  I'll list the xml document, table create stmt, and the sproc.  I can't seem to get the source of "GMAC" to insert into Prospect.Source (its an attribute), but id and request date (they're elements) load just fine.

  Argh, I can't seem to find any good samples like this and I would really appreciate any suggestions.  I wouldn't think this is a difficult thing to do.  I just haven't been able to figure it out.

  Thanks!  Kim

  Here's the XML Document (save it in c:\temp)

  <?xml version="1.0" encoding="UTF-16"?>

  <?ADF version='1.0'?>

  <adf>

  <prospect><id source="GMAC">36470</id><requestdate>2006-01-20T16:26:01</requestdate></prospect>

  </adf>

  Here's the table:

  CREATE TABLE [Prospect] (

   [id] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

   [requestdate] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

   [source] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

   CONSTRAINT [PK_Prospect] PRIMARY KEY  CLUSTERED

   (

    [id]

  &nbsp  ON [PRIMARY]

  ) ON [PRIMARY]

  GO

  Here's the sproc

  SET QUOTED_IDENTIFIER ON

  GO

  SET ANSI_NULLS ON

  GO

  /*****************Usage *************/

  /*

  exec TestOpenXML2

  select * from prospect

  delete from prospect

  */

  CREATE PROCEDURE dbo.TestOpenXML2

  AS

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

  -- Insert lines from files into temp table (using xp_cmdshell)

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

  DECLARE @strXMLText nvarchar(4000)

  -- Reading the XML data from the table into a string variable

  -- This string variable is used with OPENXML

  SELECT @strXMLText =

  CASE rowID WHEN 1

   THEN

   ISNULL(RTRIM(lineData), '')

   ELSE @strXMLText +

   ISNULL(RTRIM(lineData), '')

  END

  FROM #tmpFileLines ORDER BY rowID ASC

  print '---XML Text---'

  print @strXMLText

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

  PRINT 'Bytes read from the file:'

  PRINT DATALENGTH(@strXMLText)

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

  DROP TABLE #tmpFileLines

  -- Preparing for calling OPENXML

  DECLARE @hDoc int

  EXEC sp_xml_preparedocument @hDoc OUTPUT, @strXMLText

  print '---handle---'+cast(@hDoc as varchar)

  --so I don't have to worry about primary key violation during testing

  delete from prospect

  INSERT INTO PROSPECT(ID,requestdate,source)

   SELECT *

   from OPENXML(@hDoc,'/adf/prospect',2)

   WITH

   (id varchar(20) 'id', requestdate varchar(20) 'requestdate',source varchar(20) '@source')

  EXEC sp_xml_removedocument @hDoc

  GO

  SET QUOTED_IDENTIFIER OFF

  GO

  SET ANSI_NULLS ON

  GO

  Now execute the sproc:

  exec TestOpenXML2

  New query the table:

  id       requestdate              source

  36470 2006-01-20T16:26:01 NULL

• Kbrown

  SSCommitted

  Points: 1794

  More actions

  August 15, 2006 at 2:37 pm

  #655147

  Wouldn't you know I ran across an article shortly after I posted.

  http://www.perfectxml.com/articles/xml/openxml.asp

  And here's what I ended up with:

  INSERT INTO PROSPECT

   SELECT *

   from OPENXML(@hDoc,'/adf/prospect/id',3)

   WITH

   (id int '.', source varchar(20) '@source',daterequested varchar(20) '.')