Migrating XML trouble - 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)

Migrating XML trouble

Author

Topic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-29 : 09:15:17

[code]-- Prepare sample data
DECLARE @h INT,
@XML VARCHAR(8000),
@2k5 XML

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

-- Work the SQL Server 2000 way
EXEC sp_xml_preparedocument @h OUTPUT, @XML

SELECT emailaddress,
name,
company,
phone1, -- Correct 425-283-1480
phone2 -- Should be 555-555-1234
FROM OPENXML (@h, '/campaignrequest/requestor')
WITH (
emailaddress VARCHAR(100) 'emailaddress',
name VARCHAR(100) 'name',
company VARCHAR(100) 'company',
phone1 VARCHAR(100) 'phone',
phone2 VARCHAR(100) 'phone'
)

EXEC sp_xml_removedocument @h

-- Work the SQL Server 2005 way
SELECT T.c.query('emailaddress').value('.[1]', 'nvarchar(max)'),
T.c.query('name').value('.[1]', 'varchar(50)'),
T.c.query('company').value('.[1]', 'nvarchar(max)'),
T.c.query('phone').value('.[1]', 'nvarchar(max)') as p1,-- Should be 425-283-1480 only
T.c.query('phone').value('.[2]', 'nvarchar(max)') as p2 -- Should be 555-555-1234
FROM @2k5.nodes('/campaignrequest/requestor') AS T(c)[/code]
How to resolve this issue? I want the phone numbers as separate columns.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-29 : 09:17:57

I found the solution by trial and error...

-- Work the SQL Server 2005 way
SELECT	T.c.query('emailaddress').value('.[1]', 'nvarchar(max)'),
		T.c.query('name').value('.[1]', 'varchar(50)'),
		T.c.query('company').value('.[1]', 'nvarchar(max)'),
		T.c.query('phone[1]').value('.[1]', 'nvarchar(max)') as p1,
		T.c.query('phone[2]').value('.[1]', 'nvarchar(max)') as p2
FROM	@2k5.nodes('/campaignrequest/requestor') AS T(c)

What I can't find in Books Online, is the what [x] means when adding to the query('phone part, and when adding it to the .value('.[1] part.

And by some more TAE, I got SQL 2000 to work too.

WITH		( 
			emailaddress	VARCHAR(100)	'emailaddress',
			name		VARCHAR(100)	'name',
			company		VARCHAR(100)	'company',
			phone1		VARCHAR(100)	'phone[1]',
			phone2		VARCHAR(100)	'phone[2]'
		)

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

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-29 : 10:57:18

when doing query it means the node number
when doing at value afaik it just has to be done to ensure a singleton.
maybe it's forward thinking on the MS part when more complex xpath and xquery arrive.

_______________________________________________
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