Use same GUID twice per row

Question

I am building an application to transfer data from an SQL server to an offsite location via ftp and XML files.

I am building the XML data for each file via a query with FOR XML PATH('path'), TYPE.

I'm going to use a GUID to generate the filename as well as use as an identiifier within the file, currently my SQL to get the table is as follows (simplified):

SELECT LVL1.inv_account_no
     , LVL1.cus_postcode
     , CONVERT(varchar(255),NEWID()) + '.xml' as FileName
     , (SELECT (SELECT CONVERT(varchar(255),NEWID()) FOR XML PATH('ident'), TYPE),  (

                SELECT.... [rest of very long nested select code for generating XML]

SQL Fiddle Example

This is giving me:

Account Postcode  FileName                              xCol
AD0001  B30 3HX   2DF21466-2DA3-4D62-8B9B-FC3DF7BD1A00  <ident>656700EA-8FD5-4936-8172-0135DC49D200</ident>
AS0010  NN12 8TN  58339997-8271-4D8C-9C55-403DE98F06BE  <ident>78F8078B-629E-4906-9C6B-2AE21782DC1D</ident>

Basically different GUID's for each row/use of NEWID().

Is there a way I can insert the same GUID into both columns without incrementing a cursor or doing two updates?

Answer 1

Try something like this:

SELECT GeneratedGuid, GeneratedGuid
FROM YourTable
LEFT JOIN (SELECT NEWID() AS GeneratedGuid) AS gg ON 1 = 1

"GeneratedGuid" has a different GUID for every row.