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,nameI'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.
where type = 'U'
order by name
for xml auto
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)
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.