Re: Shaping this query

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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.


.

Relevant Pages

  • Re: Shaping this query
    ... INSERT INTO @t (FirstName, SurName, Address, Zip) ... 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 ...
    (microsoft.public.sqlserver.xml)
  • [XSLT] Can not raise some information from a XML sub-node
    ... I have to transform an input XML into an output XML using a XSLT style ... The only requirement is the error must be reported at the root level ...
    (comp.text.xml)
  • Re: output validation error
    ... The strange thing is that the output xml is making the custom functoid ok. ... > Strange Error. ...
    (microsoft.public.biztalk.general)
  • Re: How to read XML
    ... The sac.exe utility's output XML can be used as input into sac, that is, it can be used to move settings from instance A to instance B. XML is supposed to be user-friendly, you could conceivably read it with notepad. ... You could also store it in a SQL Server XML type column and do queries on it to get it into tabular format, if that's more like what you'd want to see. ...
    (microsoft.public.sqlserver.programming)
  • Re: XSL problem: deeping a flat XML file
    ... Show us what the XML for the Row element looks like? ... I suspect you aren't testing what you think you're testing. ... ASCII Ribbon Campaign | Joe Kesselman ...
    (comp.text.xml)