OPENXML in SQL Server 2000

Question

Post reply

OPENXML in SQL Server 2000

bmahony993

Right there with Babe

Points: 762
More actions
January 11, 2007 at 1:57 pm

#115685

SORRY -WRONG PLACE FOR ME TO POST 2000 QUESTIONS!

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

Refer to:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=336277

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

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 2

column names in with clause are case sensitive.

ProjID differs in case from the xml document to the with list.

www.sql-library.com[/url]