?
Solved

OPENXML Issue, not inserting the data....

Posted on 2009-04-22
4
Medium Priority
?
307 Views
Last Modified: 2012-08-13
I have a stored procedure that I am passing an XML doc to (located @ http://www.ourtinyfamily.com/sitefiles/file/Import/4222009.xml)

The issue I am having is the data that is getting inserted is all blank fields.

Why?  and how can I fix it?
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spImportUser] 
@importXML ntext,
@siteID uniqueidentifier
AS
BEGIN
Declare @Doc int
SET NOCOUNT ON;
--Create our Temp session Tables
--User Table
CREATE TABLE [dbo].[tmpUser](
	[uID]			[int] IDENTITY(1,1) NOT NULL,
	[uType]			[int],
	[uName]			[nvarchar](50),
	[pWord]			[nvarchar](50),
	[NameTitle]		[nvarchar](50) NULL,
	[FirstName]		[nvarchar](50),
	[MiddleName]	[nvarchar](50) NULL,
	[LastName]		[nvarchar](50),
	[NameSuffix]	[nvarchar](50) NULL,	
	[Addr1Type]		[int] NULL,
	[Addr1Street]	[ntext] NULL,
	[Addr1City]		[nvarchar](100) NULL,
	[Addr1State]	[int] NULL,
	[Addr1ZipCode]	[nvarchar](25) NULL,
	[Addr1Country]	[int] NULL,
	[Addr2Type]		[int] NULL,
	[Addr2Street]	[ntext] NULL,
	[Addr2City]		[nvarchar](100) NULL,
	[Addr2State]	[int] NULL,
	[Addr2ZipCode]	[nvarchar](25) NULL,
	[Addr2Country]	[int] NULL,
	[Addr3Type]		[int] NULL,
	[Addr3Street]	[ntext] NULL,
	[Addr3City]		[nvarchar](100) NULL,
	[Addr3State]	[int] NULL,
	[Addr3ZipCode]	[nvarchar](25) NULL,
	[Addr3Country]	[int] NULL,
	[Phone1Type]	[int] NULL,
	[Phone1]		[nvarchar](50) NULL,
	[Phone2Type]	[int] NULL,
	[Phone2]		[nvarchar](50) NULL,
	[Phone3Type]	[int] NULL,
	[Phone3]		[nvarchar](50) NULL,
	[Email1Type]	[int] NULL,
	[Email1]		[ntext] NULL,
	[Email2Type]	[int] NULL,
	[Email2]		[ntext] NULL,
	[Email3Type]	[int] NULL,
	[Email3]		[ntext] NULL,
	[SpouseName]	[ntext] NULL,
	[Gender]		[int] NULL,
	[BirthDate]		[datetime] NULL,
	[AnnivDate]		[datetime] NULL,
	[MStatus]		[int] NULL,
	[UserActive]	[int] NULL,
	[Misc1Name]		[nvarchar](50) NULL,
	[Misc1Value]	[ntext] NULL,
	[Misc2Name]		[nvarchar](50) NULL,
	[Misc2Value]	[ntext] NULL,
	[Misc3Name]		[nvarchar](50) NULL,
	[Misc3Value]	[ntext] NULL,
	[DateJoined]	[datetime]
)
--Prepare the XML passed to this Procedure
Exec sp_xml_preparedocument @Doc Output, @importXML
--Insert the main User Info
Insert tmpUser
(
	uType, uName, pWord, NameTitle, FirstName, MiddleName, LastName, NameSuffix, Addr1Type, 
	Addr1Street, Addr1City, Addr1State, Addr1ZipCode, Addr1Country, Addr2Type, Addr2Street,
	Addr2City, Addr2State, Addr2ZipCode, Addr2Country, Addr3Type, Addr3Street, Addr3City,
	Addr3State, Addr3ZipCode, Addr3Country, Phone1Type, Phone1, Phone2Type, Phone2, Phone3Type,
	Phone3, Email1Type, Email1, Email2Type, Email2, Email3Type, Email3, SpouseName, Gender,
	BirthDate, AnnivDate, MStatus, UserActive, Misc1Name, Misc1Value, Misc2Name, Misc2Value,
	Misc3Name, Misc3Value, DateJoined
)
Select u0, u1, u2, u3, u4, u5, u6, u7, u8, u9, u10, u11, u12, u13, u14, u15, u16, u17, u18, u19, u20,
	   u21, u22, u23, u24, u25, u26, u27, u28, u29, u30, u31, u32, u33, u34, u35, u36, u37, u38, u39,
       u40, u41, u42, u43, u44, u45, u46, u47, u48, u49, u50
From OPENXML(@Doc, 'Import/User', 2) With (
	u0		Int				'@u0',
	u1		NVarchar		'@u1',
	u2		NVarchar		'@u2',
	u3		NVarchar		'@u3',
	u4		NVarchar		'@u4',
	u5		NVarchar		'@u5',
	u6		NVarchar		'@u6',
	u7		NVarchar		'@u7',	
	u8		Int				'@u8',
	u9		NText			'@u9',
	u10		NVarchar		'@u10',
	u11		Int				'@u11',
	u12		NVarchar		'@u12',
	u13		Int				'@u13',
	u14		Int				'@u14',
	u15		NText			'@u15',
	u16		NVarchar		'@u16',
	u17		Int				'@u17',
	u18		NVarchar		'@u18',
	u19		Int				'@u19',
	u20		Int				'@u20',
	u21		NText			'@u21',
	u22		NVarchar		'@u22',
	u23		Int				'@u23',
	u24		NVarchar		'@u24',
	u25		Int				'@u25',
	u26		Int				'@u26',
	u27		NVarchar		'@u27',
	u28		Int				'@u28',
	u29		NVarchar		'@u29',
	u30		Int				'@u30',
	u31		NVarchar		'@u31',
	u32		Int				'@u32',
	u33		NText			'@u33',
	u34		Int				'@u34',
	u35		NText			'@u35',
	u36		Int				'@u36',
	u37		NText			'@u37',
	u38		NText			'@u38',
	u39		Int				'@u39',
	u40		DateTime		'@u40',
	u41		DateTime		'@u41',
	u42		Int				'@u42',
	u43		Int				'@u43',
	u44		NVarchar		'@u44',
	u45		NText			'@u45',
	u46		NVarchar		'@u46',
	u47		NText			'@u47',
	u48		NVarchar		'@u48',
	u49		NText			'@u49',
	u50		DateTime		'@u50'
)
--Remove the XML document
Exec sp_xml_removedocument @Doc
 
--More Processing will go here
 
END

Open in new window

0
Comment
Question by:kevp75
  • 2
  • 2
4 Comments
 
LVL 6

Accepted Solution

by:
mvgeertruyen earned 2000 total points
ID: 24206461
Give it a go without the @. These are elements in your xml, not attributes.
(and change the node type to element by changing the 2 to 1in OPENXML)
So
From OPENXML(@Doc, 'Import/User', 1) With (u0      Int 'u0', ...
0
 
LVL 25

Author Comment

by:kevp75
ID: 24206581
i'll give that a shot....but, ms website show 1 = attribute-eccentric, 2 = node-eccentric  (unless of course i am blind which could very well be the case,,,)
0
 
LVL 6

Expert Comment

by:mvgeertruyen
ID: 24206786
Other then the number I would select anything but attribute-centric...
(got the number for 2005, if you have 2000 that might be different)
0
 
LVL 25

Author Closing Comment

by:kevp75
ID: 31573297
Thank you!
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question