views:

56

answers:

2

From SQL Server 2005, I need to do an export for a client to an xml-like format shown below. Why they are not using XML, I don't know. Any ideas on how to do this quickly without exporting the file "FOR XML" and then writing a messy script to go through the text file and find and replace <, >, and every closing XML tag? Thank you.

START_FILE:
DATE:
COLUMN1:A
COLUMN2:B
COLUMN3:C
COLUMN1:D
COLUMN2:E
COLUMN3:F
COLUMN1:G
COLUMN2:H
COLUMN3:I
END_FILE:
+1  A: 
DECLARE @Output nvarchar(max)
SET @Output = 'START_FILE:
DATE:'

SELECT @Output = @Output + '
COLUMN1:' + Col1 + '
COLUMN2:' + Col2 + '
COLUMN3:' + Col3
FROM YourTable
ORDER BY Col1

SELECT  @Output + '
END_FILE:' AS Result
Martin Smith
Concatenation is a great idea for this type of query. However, this seems to be very CPU-hungry once @Output starts to get large. If I SELECT * to get the 100,000 rows I want in a standard query, results come back immediately. However, if I run the query concatenated like above, and only select the 10 out of 50 needed columns, the query does not want to finish. I canceled after 15 minutes. I'll try running it for longer than that overnight when I have a chance, but that seemed too long when SELECT * comes back instantly. Thanks! Any further suggestions would be great.
SomeGuy
@SomeGuy - Yes. I'm not surprised by that. Panagiotis's suggestion of doing it in SSIS seems a good one to me. or maybe you could do an unpivot query then export the results out to a text file.
Martin Smith
A: 

I went with Martin's suggestion of trying an UNPIVOT query, which was all new to me. Using SSIS, I am now exporting the query to a text file formatted exactly as I need it with a run time of just a few seconds. I am using a query like below with a ":" as a column delimiter. Great suggestion Martin!

SELECT 'START_FILE' as FieldName, '' as 'FieldValue'
UNION ALL 
select 'DATE' as FieldName, getDate() as 'FieldValue'

UNION ALL

SELECT  FieldName, FieldValue
FROM
    (
    SELECT
    Cast(Column1Name as varchar) as VendorColumn1Name,
    Cast(Column2Name as varchar) as VendorColumn2Name,
    Cast(Column3Name as varchar) as VendorColumn3Name
    FROM MyTable
    ) c
    UNPIVOT
    (
    FieldValue for FieldName IN(VendorColumn1Name, VendorColumn2Name, VendorColumn3Name)
    ) as p

UNION ALL
SELECT 'END_FILE' as FieldName, '' as 'FieldValue'
SomeGuy