Exporting data from SuperOffice as XML using SQL

When doing any type of integration between SuperOffice and an external system you might come across the need to export data from SuperOffice, and have it formatted as XML. This might sound a bit tricky, but using a properly written SQL query it's actually quite easy.

In Microsoft SQL Server you have a few underused keywords that you can use to tell MSSQL to format the result of your query as XML. Here is a simple example;

SELECT
	contact_id,
	name,
	department
FROM crm7.contact
FOR XML PATH('contact')

Notice on the last line we use "FOR XML PATH('contact')". This tells MSSQL to format the rows as an XML string, and to use the 'contact' as the element name. The result from this query looks like this;

<contact>
  <contact_id>8</contact_id>
  <name>Bama</name>
  <department></department>
</contact>
<contact>
  <contact_id>7</contact_id>
  <name>Blackwater</name>
  <department></department>
</contact>

That was a pretty simple example, but you can also do queries that are a little more complex and format the XML the way you want it. For instance you might want to add some values (like id's) as XML attributes instead of XML element children. You do that by giving it a name that starts with @.

Also, valid XML cannot contain multiple root level elements, so you probably want to surround the entire XML structure with a <superoffice> tag.

Here is an example that uses both those techniques;

SELECT
	contact_id as "@id",
	name,
	department
FROM crm7.contact
FOR XML PATH('contact'), ROOT('superoffice')

The result looks like this;

<superoffice>
	<contact id="8">
		<name>Bama</name>
		<department></department>
	</contact>
	<contact id="7">
		<name>Blackwater</name>
		<department></department>
	</contact>
</superoffice>

Finally, lets look at an example where you also include a more complex structure by introducing several levels. Here we add a list of all the persons on each contact, and include category on the contacts.

SELECT 
 	c.contact_id as "@id",
	c.name,
	c.department,
	c.number1,
	c.number2,
	c.category_idx AS "category/@id",
	cat.name AS "category/*",
	(
		SELECT
		p.person_id AS "@id",
		p.firstname AS "firstname",
		p.lastname AS "lastname"
		FROM crm7.person p
		WHERE p.contact_id = c.contact_id
		FOR XML PATH('person'), TYPE
	) AS persons
FROM crm7.contact c
LEFT JOIN crm7.category cat ON c.category_idx = cat.category_id	
FOR XML PATH('contact'), ROOT('superoffice')

The result becomes;

<superoffice>
	<contact id="1">
		<name>SuperOffice Norge AS</name>
		<department></department>
		<number1>SUPEROFFICE</number1>
		<number2>10000</number2>
		<category id="3">Leverandør</category>
		<persons>
			<person id="1">
				<firstname>Camilla Heidenreich</firstname>
				<lastname>Bommen</lastname>
			</person>
			<person id="2">
				<firstname>Henning</firstname>
				<lastname>Kind Petersen</lastname>
			</person>
			<person id="3">
				<firstname>Camilla</firstname>
				<lastname>Tandberg Lomsdalen</lastname>
			</person>
			<person id="4">
				<firstname>Øivind</firstname>
				<lastname>Urdal</lastname>
			</person>
		</persons>
	</contact>
	(....)
</superoffice>

Hope this is useful :)