Sunday, June 15, 2014

Script, store and transfer data using XML, XQuery and clipboard

When surfing SQL Server forums you might notice a lot of questions like "How do I script my data and transfer it to another server?". Of course these questions always have answers,  from "Nohow" to "Use SSMS 2012/ SSMSBoost/ SSMS Toolpack". But what can you do if you don't have neither SQL Server 2012 nor any 3rd party tools (quite useful by the way).
The answer is pretty simple: why don't you get a job XML? It's simple; It's flexible; It's powerful. And you can freely copy-paste it! So what else do you need?

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.
If you click on result column with funny name SSMS will open XML for view in new XML window:
As you can see we generated simple "plain" XML with no root element (if you like root elements you can use root argument).
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)
VoilĂ ! Data transferred from one server to other - using only power of your mind! And using XML of course :)
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.

4 comments:

  1. Interesting method! In case of small data pieces it can be quicker then exporting and importing back data to file i.n excel file. Thanks for advise!

    ReplyDelete
  2. Really interesting, it's better than using excel files and it avoids the problem with the ODBC.
    You can read a big XML using:
    DECLARE @ xml XML
    SET @ xml = (
    SELECT * FROM OPENROWSET (
    BULK 'C: \ MyXmlFile.xml' SINGLE_CLOB
    ) AS xml
    )

    ReplyDelete
    Replies
    1. I'm using this technique to load XML files up to 1.5GB :)

      Delete
  3. Hi,
    I'd like to add one more great tool with a lot of awesome features that helps a lot while working with SSMS. I've found it quite recently and really impressed here it is

    ReplyDelete