openxml

Question

openxml

bmahony993

Right there with Babe

Points: 762
More actions
January 11, 2007 at 2:07 pm

#114999

I posted this in the 2005 forum earlier - hopefully its not a problem to repost it here. My apologies if its against any forum rules...
Here is the code I am using to get xml data into a SQL table. The problem is that it does not see the value for the first variable (ProjID) and inserts a null value into the table for it. I am guessing it has something to do with the OPENXML that I am using, and I have tried different variations on what to use there, but I haven't found how to solve the problem. Any help would be appreciated!
DECLARE @iDoc int, @test-2 varchar (2000)
set @test-2 = '<?xml version="1.0" encoding="UTF-8" ?>
<dataroot xmlns:od="urn:schemas-microsoft-com:officedata" generated="2007-01-11T09:07:24">
<allProj>
<ProjID>0779-00-00-00</ProjID>
<Yr>2006</Yr>
<Title>GATES MVP Administration and Management</Title>
<Bud_Cat>MVP</Bud_Cat>
<RPM>1661</RPM>
<PADM>E104</PADM>
<ABC>E104</ABC>
<Team>MVP</Team>
<Proj_Grp>MVP</Proj_Grp>
<Prg_Goal>CCD</Prg_Goal>
<Class>Class I</Class>
<Last_Chg>2006-08-23T14:31:00</Last_Chg>
<RevCalc>1</RevCalc>
<ABC_Office>Ferney</ABC_Office>
<Calc_True>0</Calc_True>
</allProj>
</dataroot>
'
exec sp_xml_preparedocument @iDoc OUTPUT,@test
INSERT INTO TESTDATA(ProjID,Yr,Title,Bud_Cat,RPM,PADM,ABC,Team,Proj_grp,Prg_Goal,Class,Notes,Last_Chg,RevCalc,ABC_Office,Calc_True)
  (SELECT * FROM OpenXML(@iDoc, '/dataroot/allProj',3)
WITH
(ProjId VARCHAR(50),
   Yr VARCHAR(4),
   Title VARCHAR(80),
   Bud_Cat VARCHAR(50),
   RPM VARCHAR(6),
   PADM VARCHAR(6),
   ABC VARCHAR(6),
   Team VARCHAR(25),
   Proj_grp VARCHAR(15),
   Prg_Goal VARCHAR(10),
   Class VARCHAR(10),
   Notes VARCHAR(3000),
   Last_Chg SMALLDATETIME,
   RevCalc SMALLINT,
   ABC_Office VARCHAR(50),
   Calc_True BIT)
)
EXEC sp_xml_removedocument @iDoc

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

It's preferable if you don't cross post. People watch all the forums and cross posting means you get bits of answers in a couple places.

As for your openXML, very easy. XML is case sensitive. In your XML declaration you have

but in your openxml you have

ProjId

Change the case to match and it'll work fine.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

bmahony993 Right there with Babe Points: 762 More actions · Answer 2

bmahony993

Right there with Babe

Points: 762

January 11, 2007 at 2:58 pm

#682788

Thank you so much!