Parsing a XML - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Parsing a XML

Author

Topic

vmurali
Yak Posting Veteran

88 Posts

Posted - 2007-07-30 : 03:45:19

I have a xml and I need to parse it and store the values into database.

<campaignrequest>
<requestor>
<emailaddress>test@test.net</emailaddress>
<name>CS Tester</name>
<company>EEE</company>
<phone>425-283-1480</phone>
</requestor>
<sponsor>
<alias>test@test.net</alias>
<name>CCC Sponsor</name>
</sponsor>
</campaignrequest>

I need to parse the above xml and store the values in Contacts Table.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 04:07:38

Something like this

SELECT		emailaddress,
		name,
		company,
		phone
FROM		OPENXML (@h, '/campaignrequest/requestor')
WITH		( 
			emailaddress	VARCHAR(100)	'emailaddress',
			name		VARCHAR(100)	'name',
			company		VARCHAR(100)	'company',
			phone		VARCHAR(100)	'phone'
		)

SELECT		alias,
		name
FROM		OPENXML (@h, '/campaignrequest/sponsor')
WITH		( 
			alias	VARCHAR(100)	'alias',
			name	VARCHAR(100)	'name'
		)

Or try this

SELECT		emailaddress,
		name_r,
		company,
		phone,
		alias,
		name_s
FROM		OPENXML (@h, '/campaignrequest/requestor')
WITH		( 
			emailaddress	VARCHAR(100)	'emailaddress',
			name_r		VARCHAR(100)	'name',
			company		VARCHAR(100)	'company',
			phone		VARCHAR(100)	'phone',
			alias		VARCHAR(100)	'../sponsor/alias',
			name_s		VARCHAR(100)	'../sponsor/name'
		)

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 04:18:54

Complete code here

-- Prepare sample data
DECLARE	@h INT,
	@XML VARCHAR(8000)

SET	@XML = '
<campaignrequest>
	<requestor>
		<emailaddress>test@test.net</emailaddress>
		<name>CS Tester</name>
		<company>EEE</company>
		<phone>425-283-1480</phone>
	</requestor>
	<sponsor>
		<alias>test@test.net</alias>
		<name>CCC Sponsor</name>
	</sponsor>
</campaignrequest>
'

EXEC sp_xml_preparedocument @h OUTPUT, @XML

-- Get the requestors
SELECT		emailaddress,
		name,
		company,
		phone
FROM		OPENXML (@h, '/campaignrequest/requestor')
WITH		( 
			emailaddress	VARCHAR(100)	'emailaddress',
			name		VARCHAR(100)	'name',
			company		VARCHAR(100)	'company',
			phone		VARCHAR(100)	'phone'
		)

-- Get the sponsors
SELECT		alias,
		name
FROM		OPENXML (@h, '/campaignrequest/sponsor')
WITH		( 
			alias	VARCHAR(100)	'alias',
			name	VARCHAR(100)	'name'
		)

-- Get all 1
SELECT		emailaddress,
		name_r,
		company,
		phone,
		alias,
		name_s
FROM		OPENXML (@h, '/campaignrequest/requestor')
WITH		( 
			emailaddress	VARCHAR(100)	'emailaddress',
			name_r		VARCHAR(100)	'name',
			company		VARCHAR(100)	'company',
			phone		VARCHAR(100)	'phone',
			alias		VARCHAR(100)	'../sponsor/alias',
			name_s		VARCHAR(100)	'../sponsor/name'
		)

-- Get all 2
SELECT		emailaddress,
		name_r,
		company,
		phone,
		alias,
		name_s
FROM		OPENXML (@h, '/campaignrequest/sponsor')
WITH		( 
			emailaddress	VARCHAR(100)	'../requestor/emailaddress',
			name_r		VARCHAR(100)	'../requestor/name',
			company		VARCHAR(100)	'../requestor/company',
			phone		VARCHAR(100)	'../requestor/phone',
			alias		VARCHAR(100)	'alias',
			name_s		VARCHAR(100)	'name'
		)

-- Get all 3
SELECT		emailaddress,
		name_r,
		company,
		phone,
		alias,
		name_s
FROM		OPENXML (@h, '/campaignrequest')
WITH		( 
			emailaddress	VARCHAR(100)	'requestor/emailaddress',
			name_r		VARCHAR(100)	'requestor/name',
			company		VARCHAR(100)	'requestor/company',
			phone		VARCHAR(100)	'requestor/phone',
			alias		VARCHAR(100)	'sponsor/alias',
			name_s		VARCHAR(100)	'sponsor/name'
		)

EXEC sp_xml_removedocument @h

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-30 : 04:30:39

and using the built in xml datatype functions:


declare @xml XML
SELECT @xml = '<campaignrequest>
<requestor>
<emailaddress>test@test.net</emailaddress>
<name>CS Tester</name>
<company>EEE</company>
<phone>425-283-1480</phone>
</requestor>
<sponsor>
<alias>test@test.net</alias>
<name>CCC Sponsor</name>
</sponsor>
</campaignrequest>'

-- in one table
SELECT	-- requestor part
		T.c.query('requestor/emailaddress').value('.[1]', 'nvarchar(max)'),
		T.c.query('requestor/name').value('.[1]', 'nvarchar(max)'),
		T.c.query('requestor/company').value('.[1]', 'nvarchar(max)'),
		T.c.query('requestor/phone').value('.[1]', 'nvarchar(max)'),
		-- sponsor part
		T.c.query('sponsor/alias').value('.[1]', 'nvarchar(max)'),
		T.c.query('sponsor/name').value('.[1]', 'nvarchar(max)')
FROM	@xml.nodes('/campaignrequest') T(c)

-- in two tables

-- requestor part
SELECT	T.c.query('emailaddress').value('.[1]', 'nvarchar(max)'),
		T.c.query('name').value('.[1]', 'nvarchar(max)'),
		T.c.query('company').value('.[1]', 'nvarchar(max)'),
		T.c.query('phone').value('.[1]', 'nvarchar(max)')
FROM	@xml.nodes('/campaignrequest/requestor') T(c)

-- sponsor part
SELECT	T.c.query('alias').value('.[1]', 'nvarchar(max)'),
		T.c.query('name').value('.[1]', 'nvarchar(max)')		
FROM	@xml.nodes('/campaignrequest/sponsor') T(c)

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources