Re: Shaping this query

Tech-Archive recommends: Speed Up your PC by fixing your registry

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" <Bob@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:53779C0F-3923-4044-A788-2134234A34F2@xxxxxxxxxxxxxxxx
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:

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'

SELECT
(SELECT * FROM @t FOR XML RAW('Address'),TYPE)
..query('
for $row in (/Address)
return (
<Address>
<Item Key="FirstName" Value="{$row/@FirstName}"/>
<Item Key="SurName" Value="{$row/@SurName}"/>
<Item Key="Address" Value="{$row/@Address}"/>
<Item Key="Zip" Value="{$row/@Zip}"/>
</Address>
)
')
..query('<Addresses>{/}</Addresses>')

/*
<Addresses>
<Address>
<Item Key="FirstName" Value="Jacob" />
<Item Key="SurName" Value="Sebastian" />
<Item Key="Address" Value="4th street" />
<Item Key="Zip" Value="38005" />
</Address>
<Address>
<Item Key="FirstName" Value="Smith" />
<Item Key="SurName" Value="Jones" />
<Item Key="Address" Value="9th street" />
<Item Key="Zip" Value="10002" />
</Address>
</Addresses>
*/


"Bob" <Bob@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:75D51784-5FD4-4618-B812-C963046D12F5@xxxxxxxxxxxxxxxx
> 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
>
> SELECT
> (
> SELECT 'Firstname' AS [key], Firstname AS [value]
> FROM #addresses item
> WHERE a.id = item.id
> FOR XML AUTO, TYPE
> ),
> (
> SELECT 'Surname' AS [key], Surname AS [value]
> FROM #addresses item
> WHERE a.id = item.id
> FOR XML AUTO, TYPE
> ),
> (
> SELECT 'Address1' AS [key], Address1 AS [value]
> FROM #addresses item
> WHERE a.id = item.id
> FOR XML AUTO, TYPE
> )
> ,
> (
> SELECT 'Zip' AS [key], Zip AS [value]
> FROM #addresses item
> WHERE a.id = item.id
> FOR XML AUTO, TYPE
> )
> FROM #addresses a
> FOR XML RAW( 'address' ), ROOT( 'addresses' )
>
> Dunno if there's an easier way!
> HTH
> wBob
>
> "spondishy@xxxxxxxxxxxxxx" wrote:
>
>> 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:
>>
>> <addresses>
>> <address>
>> <item key="Firstname" value="John" />
>> <item key="Surname" value="Smith" />
>> <item key="Address1" value="The Street" />
>> <item key="Zip" value="12345" />
>> </address>
>> <address>
>> ........
>> </address>
>> </addresses.
>>
>> I'm getting close, but I'm not quite there. Any help appreciated.
>>

.