Hi,

I'm having a go at an xml query but I'm having problems getting the
correct results with the output xml. Say I have the following flat
table:

Firstname,Surname,Address1,Zip

I want my xml output to be:

........

I'm getting close, but I'm not quite there. Any help appreciated.

I could this to work if there was a unique key in the #addresses table:

DROP TABLE #addresses
GO
CREATE TABLE #addresses ( id INT IDENTITY PRIMARY KEY, Firstname
VARCHAR(50), Surname VARCHAR(50), Address1 VARCHAR(50), Zip VARCHAR(10) )
GO

INSERT INTO #addresses VALUES ( 'John', 'Smith', 'The Street', 12345 )
INSERT INTO #addresses VALUES ( 'Sue', 'Jones', 'The Grove', 34678 )
GO

Another Option

DECLARE @t TABLE (FirstName VARCHAR(10), SurName VARCHAR(10), Address
VARCHAR(10), Zip VARCHAR(5))
INSERT INTO @t (FirstName, SurName, Address, Zip)
SELECT 'Jacob', 'Sebastian', '4th street', '38005'

INSERT INTO @t (FirstName, SurName, Address, Zip)
SELECT 'Smith', 'Jones', '9th street', '10002'

Nice use of FLOWR statement!

Having checked the execution plans, the first method is much more performant
though, that iteration is costing you big-time!

I suspect there is still an easier way.

"Jacob Sebastian" wrote:

Agreed :-)

--
Jacob Sebastian
SQL Server MVP
http://www.sqlserverandxml.com

"Bob" discussions.microsoft.com> wrote in message
news:53779C0F-3923-4044-A788-2134234A34F2@microsoft.com...

Hi Bob,
could you contact me off the list?
my email is jacob[dot]reliancesp[at]gmail[dot]com

thanks
Jacob

--
Jacob Sebastian
SQL Server MVP
http://www.sqlserverandxml.com

"Bob" discussions.microsoft.com> wrote in message
news:53779C0F-3923-4044-A788-2134234A34F2@microsoft.com...

Had another think about this, and this works really well, winning the battle
of the execution plans too:

CREATE TABLE #addresses ( id INT IDENTITY PRIMARY KEY, Firstname
VARCHAR(50), Surname VARCHAR(50), Address1 VARCHAR(50), Zip VARCHAR(10) )
GO

INSERT INTO #addresses VALUES ( 'John', 'Smith', 'The Street', 12345 )
INSERT INTO #addresses VALUES ( 'Sue', 'Jones', 'The Grove', 34678 )
GO

SELECT CAST( '


' AS XML )
FROM #addresses
FOR XML RAW( 'address' ), ROOT( 'addresses' ), TYPE

"spondishy@googlemail.com" wrote:

nice idea Bob.
the only downside I can think of is about having special characters in the
attribute value. For example, if the name contains a double quotes or
less-than sign, this will not work.
But I don’t think such cases will be rare.

--
Jacob Sebastian
SQL Server MVP
http://www.sqlserverandxml.com

"Bob" discussions.microsoft.com> wrote in message
news:D3D4A98A-A2BB-4F38-ADD4-EF5D104813B6@microsoft.com...