OPENXML function - 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 2000 Forums

Transact-SQL (2000)

OPENXML function

Author

Topic

SoftFox
Starting Member

42 Posts

Posted - 2007-10-03 : 04:09:00

I am using OPENXML function to parse XML into a dataset. Part of the XML file is organised as follows:

<common:InternationalAddress>
<apd:IntAddressLine>10</apd:IntAddressLine>
<apd:IntAddressLine>Red Street</apd:IntAddressLine>
<apd:IntAddressLine>Middlesbrough</apd:IntAddressLine>
<apd:IntAddressLine>Clevelend</apd:IntAddressLine>
<apd:Country>Test</apd:Country>
<apd:InternationalPostCode>Test</apd:InternationalPostCode>
</common:InternationalAddress>

Where the element IntAddressLine is repeated multiple times for each line of the address. I want to select this in the following format:

10 Red Street Middlesbrough Cleveland.

So far i can either select all the element values concatenated together like this:

10RedStreetMiddlesbroughCleveland

using the following query:

SELECT *
FROM OPENXML(@docHandle, '/Proposal', 2)
WITH
(
IntAddressLine VARCHAR(200) 'Agent/ExternalAddress/InternationalAddress' --IntAddressLine
)

OR I can select it in seperate rows like this:

10
Red Street
Middlesbrough
Cleveland

This then requires me to use a cursor to pivot this into one row which seems a very heavy handed way to do it.

My question is, is there a way to use OPENXML to format it as i require without resorting to using a cursort to pivot the above result into one row?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-03 : 04:35:34

[code]DECLARE @XML VARCHAR(8000)

SET @XML = '<InternationalAddress>
<IntAddressLine>10</IntAddressLine>
<IntAddressLine>Red Street</IntAddressLine>
<IntAddressLine>Middlesbrough</IntAddressLine>
<IntAddressLine>Clevelend</IntAddressLine>
<Country>Test</Country>
<InternationalPostCode>Test</InternationalPostCode>
</InternationalAddress>'

DECLARE @docHandle INT

EXEC sp_xml_preparedocument @docHandle OUTPUT, @XML

SELECT *
FROM OPENXML(@docHandle, '/InternationalAddress', 2)
WITH (
IntAddressLine1 VARCHAR(200) 'IntAddressLine[1]',
IntAddressLine2 VARCHAR(200) 'IntAddressLine[2]',
IntAddressLine3 VARCHAR(200) 'IntAddressLine[3]',
IntAddressLine4 VARCHAR(200) 'IntAddressLine[4]'
)

EXEC sp_xml_removedocument @docHandle[/code]

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

SoftFox
Starting Member

42 Posts

Posted - 2007-10-03 : 04:41:39

Brilliant. Much appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources