SQL select to XML

There are a couple of different ways to deal with getting XML out of MS SQL. A nice post from “beyond relational” by Madhivanan  is this: http://beyondrelational.com/modules/2/blogs/70/Posts/17761/export-table-data-in-xml-format.aspx?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+beyondrelationalmain+%28Technology+stream+from+beyondrelational.com%29.  The URL is a bit messy.

The SQL code is this: (notice if the XML is longer than 8000 characters – not in this case – we will get truncated).

declare @sql varchar(8000)
set @sql=”
select @sql=@sql+
‘select ”<‘+table_name+’>”+(select * from ‘+table_name+’ for xml raw(””),elements)+’+”'</’+TABLE_NAME+’>” as t’
from
information_schema.tables
where
table_name=’cartridge’
exec(@sql)

output looks like this

<cartridge>
<pk1>1</pk1><dtcreated>2011-10-04T18:32:42.083</dtcreated><dtmodified>2011-10-04T18:32:42.083</dtmodified><cartridge_id>PL10239</cartridge_id><publisher_name>Addison Wesley/Benjamin Cummings</publisher_name><title>General, Organic, and Biological Chemistry, Structures of Life, 2e - Copy Protected</title><instid>0</instid><orgid>0</orgid><type>C</type>

<pk1>2</pk1><dtcreated>2011-10-10T15:59:00.533</dtcreated><dtmodified>2011-10-10T15:59:00.533</dtmodified><cartridge_id>MO10136</cartridge_id><publisher_name>Mosby, Inc.</publisher_name><title>Health Assessment Online for Mosby's Guide To Physical Examination, 6th Edition (Bb 6.x version)</title><instid>0</instid><orgid>0</orgid><type>C</type>
 ...
<pk1>9</pk1><dtcreated>2012-06-25T10:46:26.517</dtcreated><dtmodified>2012-06-25T10:46:26.517</dtmodified><cartridge_id>MO10243</cartridge_id><publisher_name>Mosby, Inc.</publisher_name><title>Nursing Skills Online 2.0 for Fundamentals of Nursing, 8th Edition</title><instid>0</instid><orgid>0</orgid><type>C</type>
 </cartridge>

More directly, we could just query

select * from cartridge for xml raw

and get this: (i could not report on all rows since in this case, since we are not writing to a variable, the length of line constrain did truncate output – this is true of the remaining XML queries as well).  We get one element per row and all data is attributes.

<row pk1="1" dtcreated="2011-10-04T18:32:42.083" dtmodified="2011-10-04T18:32:42.083" cartridge_id="PL10239" publisher_name="Addison Wesley/Benjamin Cummings" title="General, Organic, and Biological Chemistry, Structures of Life, 2e - Copy Protected" instid="0" orgid="0" type="C"/>

<row pk1="2" dtcreated="2011-10-10T15:59:00.533" dtmodified="2011-10-10T15:59:00.533" cartridge_id="MO10136" publisher_name="Mosby, Inc." title="Health Assessment Online for Mosby's Guide To Physical Examination, 6th Edition (Bb 6.x version)" instid="0" orgid="0" type="C"/>

or

Select * from cartridge for xml auto (the element is named for the dataset, not just <row/>

<cartridge pk1="1" dtcreated="2011-10-04T18:32:42.083" dtmodified="2011-10-04T18:32:42.083" cartridge_id="PL10239" publisher_name="Addison Wesley/Benjamin Cummings" title="General, Organic, and Biological Chemistry, Structures of Life, 2e - Copy Protected" instid="0" orgid="0" type="C"/>

<cartridge pk1="2" dtcreated="2011-10-10T15:59:00.533" dtmodified="2011-10-10T15:59:00.533" cartridge_id="MO10136" publisher_name="Mosby, Inc." title="Health Assessment Online for Mosby's Guide To Physical Examination, 6th Edition (Bb 6.x version)" instid="0" orgid="0" type="C"/>

or

select * from cartridge for xml auto, elements – which gets us back to what we started out with

<cartridge><pk1>1</pk1><dtcreated>2011-10-04T18:32:42.083</dtcreated><dtmodified>2011-10-04T18:32:42.083</dtmodified><cartridge_id>PL10239</cartridge_id><publisher_name>Addison Wesley/Benjamin Cummings</publisher_name><title>General, Organic, and Biological Chemistry, Structures of Life, 2e - Copy Protected</title><instid>0</instid><orgid>0</orgid><type>C</type></cartridge>

<cartridge><pk1>2</pk1><dtcreated>2011-10-10T15:59:00.533</dtcreated><dtmodified>2011-10-10T15:59:00.533</dtmodified><cartridge_id>MO10136</cartridge_id><publisher_name>Mosby, Inc.</publisher_name><title>Health Assessment Online for Mosby's Guide To Physical Examination, 6th Edition (Bb 6.x version)</title><instid>0</instid><orgid>0</orgid><type>C</type></cartridge>
Advertisements
This entry was posted in sql. Bookmark the permalink.