Marik Marik - 2 months ago 7
C# Question

T-SQL to XML to C# Back to SQL

I'm very new to SQL, C# and such.
But I'm learning each day, which is fun!

I need to take info from 2 tables in 2 different databases in MSSQL.
Turn it into an .xml file, bubble sort it in C# and transfer it back into a 3rd database in MSSQL. I have made the code to turn the tables into xml files,
my question here is if I can make them into just ONE .xml file.
The code I've learned creates one for each table, so 2 tables from 2 databases gives me 4 .xml files.

SELECT * FROM [Database1].[dbo].[Table_1]
ORDER BY NEWID()
FOR XML AUTO

SELECT *
FROM [Database1].[dbo].[Table_2]
ORDER BY NEWID()
FOR XML AUTO

SELECT *
FROM [Database2].[dbo].[Table_1]
FOR XML AUTO

SELECT *
FROM [Database2].[dbo].[Table_2]
FOR XML AUTO


After It's turned to an xml file I must put it in C# and bubble sort it into the 3rd database in MSSQL, as you can see I can atleast take the data I want, but when it comes to C# and bubble sort it I'm compleatly lost. I've googled around but I'm having trubble understanding so I was hoping you guys could try and explain in a more easier way.

Answer

One part of your question was: How do I get 4 XML results into one:

These are 4 different calls, two against the current Db, two against another Db:

SELECT TOP 3 * 
FROM INFORMATION_SCHEMA.TABLES
FOR XML RAW('INFORMATION_SCHEMA.TABLES'),ROOT('CurrentDb')

SELECT TOP 3 * 
FROM INFORMATION_SCHEMA.COLUMNS
FOR XML RAW('INFORMATION_SCHEMA.COLUMNS'),ROOT('CurrentDb')

SELECT TOP 3 * 
FROM OtherDb.INFORMATION_SCHEMA.TABLES
FOR XML RAW('INFORMATION_SCHEMA.TABLES'),ROOT('OtherDb')

SELECT TOP 3 * 
FROM OtherDb.INFORMATION_SCHEMA.COLUMNS
FOR XML RAW('INFORMATION_SCHEMA.COLUMNS'),ROOT('OtherDb')

To get the same together in one, you just have to wrap this within an outer select. And be aware of the ,TYPE...

Some explanation: If you write SELECT * FROM xyz FOR XML RAW you'll get an XML back. This is a full and complex structure but still a scalar value.

Write SELECT (SELECT * FROM xyz FOR XML RAW) AS MyXML,'test' AS OtherColumn FROM SomeTable. You'll get your XML, but as escaped text representation. You must specify the ,TYPE to get a real XML back.

SELECT
(
    SELECT TOP 3 * 
    FROM INFORMATION_SCHEMA.TABLES
    FOR XML RAW('INFORMATION_SCHEMA.TABLES'),ROOT('CurrentDb'),TYPE
)
,(
    SELECT TOP 3 * 
    FROM INFORMATION_SCHEMA.COLUMNS
    FOR XML RAW('INFORMATION_SCHEMA.COLUMNS'),ROOT('CurrentDb'),TYPE
)
,(
    SELECT TOP 3 * 
    FROM OtherDb.INFORMATION_SCHEMA.TABLES
    FOR XML RAW('INFORMATION_SCHEMA.TABLES'),ROOT('OtherDb'),TYPE
)
,(
    SELECT TOP 3 * 
    FROM OtherDb.INFORMATION_SCHEMA.COLUMNS
    FOR XML RAW('INFORMATION_SCHEMA.COLUMNS'),ROOT('OtherDb'),TYPE
)
FOR XML RAW
Comments