{"id":626,"date":"2012-02-01T12:11:49","date_gmt":"2012-02-01T17:11:49","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=626"},"modified":"2014-11-13T14:00:06","modified_gmt":"2014-11-13T19:00:06","slug":"selecting-the-entire-database-as-xml-string-2","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/626\/selecting-the-entire-database-as-xml-string-2\/","title":{"rendered":"Selecting the entire Database as XML String – 2"},"content":{"rendered":"
\n

\nAbout two weeks ago I wrote about how to get the content of the entire database into an XML document<\/a>. Today Daniel commented<\/a>, that he would like the XML to be in a compacter format. In this post I am going to explain how to get there.\n<\/p>\n

\nWhile WordPress™ conveniently ate the example XML Daniel tried to post, I will assume for this article that he was going for this format:\n<\/p>\n

\n[xml]\n<tables>
\n <table name="[dbo].[T1366]">
\n <row id="1" c1="1366" c2="2732" c3="4098" c4="5464" c5="6830" c6="8196" \/>
\n <row id="2" c1="1366" c2="2732" c3="4098" c4="5464" c5="6830" c6="8196" \/>
\n <\/table>
\n <table name="[dbo].[T127]">
\n <row id="1" c1="127" c2="254" c3="381" c4="508" c5="635" c6="762" c7="889" \/>
\n <row id="2" c1="127" c2="254" c3="381" c4="508" c5="635" c6="762" c7="889" \/>
\n <\/table>
\n<\/tables>
\n[\/xml]\n<\/div>\n

\nInstead of all column values being sub-nodes of their <row> nodes they are now attributes of an empty <row> node. Also the <data> node between <table> and <row> is now missing, as it was kind of superfluous. And while I was changing the code anyway, I went ahead and added the previously missing root node as <tables>.\n<\/p>\n

\nSo how did I get there?\n<\/p>\n

\nFirst let us look at the missing root node, which turns out to be the simplest of all the changes. When you select rows for a table specifying the FOR XML PATH('NodeName') directive, every row is going to be represented as a node with the passed in name. Those nodes are just hanging together without an enclosing root node. To make the output a valid XML document you can just specify the ROOT clause next to the PATH clause like this:\n<\/p>\n

\n[sql]\n SELECT *
\n FROM dbo.Table
\n FOR XML PATH('rowNodeName'),ROOT('rootNodeName'),TYPE
\n[\/sql]\n<\/div>\n

\nThe TYPE directive tells SQL Server to return the XML as a value with the XML type. If you leave it out the XML gets returned as a string, which can create problems if you want to continue to work with it in T-SQL.\n<\/p>\n

\nThe next step was, to get rid of the <data> node. When you specify an XML value in a select statement that is in turn using FOR XML PATH, that value is wrapped in a node with the value's column name. The generated SQL in my original solution looked like this:\n<\/p>\n

\n[sql]\nSELECT * FROM(
\nSELECT '[dbo].[t1]' [@name],(SELECT * FROM dbo.t1 FOR XML PATH('row'),TYPE) data
\nUNION ALL
\nSELECT '[dbo].[t2]' [@name],(SELECT * FROM dbo.t2 FOR XML PATH('row'),TYPE) data
\n)X FOR XML PATH('table'),TYPE;
\n[\/sql]\n<\/div>\n

\nThe data column in the UNION ALL query contains XML values, so the outer SELECT * ... FOR XML PATH('table') is going to wrap each one into a <data> node like this:\n<\/p>\n

\n[xml]\n<table name="[dbo].[t1]">
\n <data>
\n <row>
\n <id>1<\/id>
\n <c1>1<\/c1>
\n <\/row>
\n <row>
\n <id>2<\/id>
\n <c1>1<\/c1>
\n <\/row>
\n <\/data>
\n<\/table>
\n<table name="[dbo].[t2]">
\n <data>
\n <row>
\n <id>1<\/id>
\n <c1>2<\/c1>
\n <c2>4<\/c2>
\n <\/row>
\n <row>
\n <id>2<\/id>
\n <c1>2<\/c1>
\n <c2>4<\/c2>
\n <\/row>
\n <\/data>
\n<\/table>
\n[\/xml]\n<\/div>\n

\nTo remove the additional node we can use the fact that SQL Sever will inline the value of a column that does not have a name. So we need to select the data column without specifying its name. To do that we will use a trick:\n<\/p>\n

\n[sql]\nSELECT [@name],(SELECT data) FROM(
\nSELECT '[dbo].[t1]' [@name],(SELECT * FROM dbo.t1 FOR XML PATH('row'),TYPE) data
\nUNION ALL
\nSELECT '[dbo].[t2]' [@name],(SELECT * FROM dbo.t2 FOR XML PATH('row'),TYPE) data
\n)X FOR XML PATH('table'),TYPE;
\n[\/sql]\n<\/div>\n

\nBy wrapping the data column in its own SELECT it loses its name property for the outer SELECT statement. The output it generates now looks like this:\n<\/p>\n

\n[sql]\n<table name="[dbo].[t1]">
\n <row>
\n <id>1<\/id>
\n <c1>1<\/c1>
\n <\/row>
\n <row>
\n <id>2<\/id>
\n <c1>1<\/c1>
\n <\/row>
\n<\/table>
\n<table name="[dbo].[t2]">
\n <row>
\n <id>1<\/id>
\n <c1>2<\/c1>
\n <c2>4<\/c2>
\n <\/row>
\n <row>
\n <id>2<\/id>
\n <c1>2<\/c1>
\n <c2>4<\/c2>
\n <\/row>
\n<\/table>
\n[\/sql]\n<\/div>\n

\n The last thing we need to accomplish is to make the column values attributes of each <row> node instead of sub-nodes. If you specify a column name with a leading \"@\" sign, FOR XML PATH will make that value an attribute with that name (without the \"@\"):\n<\/p>\n

\n[sql]\nSELECT id AS [@id] FROM dbo.t1 FOR XML PATH('row'),TYPE;
\n[\/sql]\n
\n

\nThe output of above statement looks like this:<\/p>\n

\n[xml]\n<row id="1" \/>
\n<row id="2" \/>
\n[\/xml]\n<\/div>\n

\nSo instead of a SELECT * FROM for each table, we need to list all columns in the format shown above. To get this specially formatted column list we are going to use XML concatenation<\/a> one more time:\n<\/p>\n

\n[sql]\nSELECT STUFF((SELECT ','+QUOTENAME(name)+' AS '+QUOTENAME('@'+name)
\n FROM sys.columns c
\n WHERE c.object_id = OBJECT_ID('dbo.t2')
\n ORDER BY column_id
\n FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,1,'');
\n[\/sql]\n<\/div>\n

\nThis produces the desired list:<\/p>\n

[id] AS [@id],[c1] AS [@c1],[c2] AS [@c2]<\/pre>\n<\/p>\n
\nWith that we have all the parts collected to create the new \"compact\" solution. The last step is to put it all together:\n<\/p>\n
\n[sql]\nDECLARE @cmd NVARCHAR(MAX) ;
\nSET @cmd = 'SELECT [@name],(SELECT data) FROM('
\n    + STUFF(
\n(SELECT ' UNION ALL SELECT ''' + QUOTENAME(OBJECT_SCHEMA_NAME(object_id))
\n        + '.' + QUOTENAME(t.name) + ''' [@name],' + '(SELECT '+c.ColList+' FROM '
\n        + QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' + QUOTENAME(t.name)
\n        + ' FOR XML PATH(''row''),TYPE) data'
\n FROM   sys.tables t
\n CROSS APPLY (SELECT STUFF((SELECT ','+QUOTENAME(name)+' AS '+QUOTENAME('@'+name)
\n                              FROM sys.columns c
\n                             WHERE c.object_id = t.object_id
\n                             ORDER BY column_id
\n                            FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,1,'')
\n             )c(ColList)
\n    FOR     XML PATH('') ,
\n                TYPE).value('.', 'NVARCHAR(MAX)'), 1, 11, '')
\n    + ')X FOR XML PATH(''table''),ROOT(''tables''),TYPE;' ;<\/p>\n
EXEC(@cmd) ;
\n[\/sql]\n<\/p><\/div>\n
\nThis produces the desired output that was shown all the way at the beginning of this article. The size of the resulting XML document for my example database went from about 1.4 MB for the original solution to now about 0.5 MB – a significant improvement.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
About two weeks ago I wrote about how to get the content of the entire database into an XML document. Today Daniel commented, that he would like the XML to be in a compacter format. In this post I am [more…]<\/a><\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[5,16],"tags":[],"class_list":["post-626","post","type-post","status-publish","format-standard","hentry","category-general","category-xml"],"acf":[],"yoast_head":"\nSelecting the entire Database as XML String - 2 - sqlity.net<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/sqlity.net\/en\/626\/selecting-the-entire-database-as-xml-string-2\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Selecting the entire Database as XML String - 2 - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"About two weeks ago I wrote about how to get the content of the entire database into an XML document. Today Daniel commented, that he would like the XML to be in a compacter format. In this post I am [more...]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/626\/selecting-the-entire-database-as-xml-string-2\/\" \/>\n<meta property=\"og:site_name\" content=\"sqlity.net\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/sqlity.net\" \/>\n<meta property=\"article:published_time\" content=\"2012-02-01T17:11:49+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T19:00:06+00:00\" \/>\n<meta name=\"author\" content=\"Sebastian Meine\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@sqlity\" \/>\n<meta name=\"twitter:site\" content=\"@sqlity\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sebastian Meine\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/626\\\/selecting-the-entire-database-as-xml-string-2\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/626\\\/selecting-the-entire-database-as-xml-string-2\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"Selecting the entire Database as XML String – 2\",\"datePublished\":\"2012-02-01T17:11:49+00:00\",\"dateModified\":\"2014-11-13T19:00:06+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/626\\\/selecting-the-entire-database-as-xml-string-2\\\/\"},\"wordCount\":1275,\"commentCount\":4,\"articleSection\":[\"General\",\"XML\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/626\\\/selecting-the-entire-database-as-xml-string-2\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/626\\\/selecting-the-entire-database-as-xml-string-2\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/626\\\/selecting-the-entire-database-as-xml-string-2\\\/\",\"name\":\"Selecting the entire Database as XML String - 2 - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"datePublished\":\"2012-02-01T17:11:49+00:00\",\"dateModified\":\"2014-11-13T19:00:06+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/626\\\/selecting-the-entire-database-as-xml-string-2\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/626\\\/selecting-the-entire-database-as-xml-string-2\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/626\\\/selecting-the-entire-database-as-xml-string-2\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Selecting the entire Database as XML String – 2\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\",\"name\":\"sqlity.net\",\"description\":\"Quality for SQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/sqlity.net\\\/en\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\",\"name\":\"Sebastian Meine\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"caption\":\"Sebastian Meine\"},\"sameAs\":[\"http:\\\/\\\/sqlity.net\",\"https:\\\/\\\/x.com\\\/sqlity\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Selecting the entire Database as XML String - 2 - sqlity.net","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/sqlity.net\/en\/626\/selecting-the-entire-database-as-xml-string-2\/","og_locale":"en_US","og_type":"article","og_title":"Selecting the entire Database as XML String - 2 - sqlity.net","og_description":"About two weeks ago I wrote about how to get the content of the entire database into an XML document. Today Daniel commented, that he would like the XML to be in a compacter format. In this post I am [more...]","og_url":"https:\/\/sqlity.net\/en\/626\/selecting-the-entire-database-as-xml-string-2\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2012-02-01T17:11:49+00:00","article_modified_time":"2014-11-13T19:00:06+00:00","author":"Sebastian Meine","twitter_card":"summary_large_image","twitter_creator":"@sqlity","twitter_site":"@sqlity","twitter_misc":{"Written by":"Sebastian Meine","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/626\/selecting-the-entire-database-as-xml-string-2\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/626\/selecting-the-entire-database-as-xml-string-2\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"Selecting the entire Database as XML String – 2","datePublished":"2012-02-01T17:11:49+00:00","dateModified":"2014-11-13T19:00:06+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/626\/selecting-the-entire-database-as-xml-string-2\/"},"wordCount":1275,"commentCount":4,"articleSection":["General","XML"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/626\/selecting-the-entire-database-as-xml-string-2\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/626\/selecting-the-entire-database-as-xml-string-2\/","url":"https:\/\/sqlity.net\/en\/626\/selecting-the-entire-database-as-xml-string-2\/","name":"Selecting the entire Database as XML String - 2 - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"datePublished":"2012-02-01T17:11:49+00:00","dateModified":"2014-11-13T19:00:06+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/626\/selecting-the-entire-database-as-xml-string-2\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/626\/selecting-the-entire-database-as-xml-string-2\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/626\/selecting-the-entire-database-as-xml-string-2\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"Selecting the entire Database as XML String – 2"}]},{"@type":"WebSite","@id":"https:\/\/sqlity.net\/en\/#website","url":"https:\/\/sqlity.net\/en\/","name":"sqlity.net","description":"Quality for SQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/sqlity.net\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c","name":"Sebastian Meine","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","caption":"Sebastian Meine"},"sameAs":["http:\/\/sqlity.net","https:\/\/x.com\/sqlity"]}]}},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-a6","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/626","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/comments?post=626"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/626\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=626"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=626"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=626"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}