The answer is pretty simple: why don't you get a
Not let grass grow under feet!
First - convert existing data to XML.
Let's take for example sys.objects table (I like to use system objects for sample select query - they are always present and always filled with predictable data. If they not - you did something wrong with you SQL Server!)
Use the power of FOR XML clause, Luke!
select object_id,name
from sys.objects
where type = 'U'
order by name
for xml auto
I've used FOR XML AUTO mode because in this example I don't care about beauty of resulted XML - I simply trying to transfer my data in quickest manner. You can play with other FOR XML modes to make your XML as pretty as you wish.
Second - transfer XML to destination server. Almost always I'm using copy-past. Works like a magic! Copy here - paste there. Even through terminal client.
Third - use XQuery to extract data from XML.
declare @xml xml
set @xml =
'<sys.objects object_id="53575229" name="Address" />
<sys.objects object_id="101575400" name="AddressType" />
<sys.objects object_id="149575571" name="AWBuildVersion" />
<sys.objects object_id="181575685" name="BillOfMaterials" />
'
select b.value('@object_id','int') as object_id
,b.value('@name','sysname') as name
from @xml.nodes('/sys.objects') a(b)
Using XML you can transfer not only results of plain SELECT statement but complex (even - very complex!) entities with many nested objects, data layers and so on. Try to dig FOR XML TYPE directive.
Also using XML you can incorporate initial data or some predefined setting/option values in creation scripts for your database, without need to have deal with all these BCP, SSIS and other data load tools.
