Fun with serialization

One of my favorite things to do with SQL Sever is XML. There are just all kinds to idiosyncrasies; however, after spending a substantial amount of time formatting xmlthere where a few things I didn't want to have to find again.

	/*
	Descriptions: reference for all the xml shapping I've learned
	Author: Mattehw Naul
	Explanation:
	for each person do a correlated query for phone numbers,
	format them mixing attributes and element values,
	serialize to xml with "TYPE" so that we return on column of type XML root tag (phonelist)
	name the cross apply result,
	when phonelist(col) is null add nil phonelist node
	*/
	-- quick inline test data
	WITH
	Person(id, FirstName, LastName) AS
	(
	SELECT 1, 'Bob', 'Smith' UNION ALL
	SELECT 2, 'Jane', 'Doe' UNION ALL
	SELECT 3, 'John', 'Jones'
	),
	PhoneTable(personid, ord, phonetype, number) AS
	(
	SELECT 1, 1, 'home', '806-555-1234' UNION ALL
	SELECT 1, 2, 'work', '806-555-2843' UNION ALL
	SELECT 2, 1, 'work', '806-555-0282' UNION ALL
	SELECT 2, 2, 'cell', '806-555-9028' UNION ALL
	SELECT 2, 3, 'home', '806-555-2103'
	)
	SELECT
	FirstName
	,LastName
	,calc.phonelist
	FROM Person
	-- wrapping the sub-select in `cross apply` is what gives us a nil atrib on list tag
	CROSS APPLY (
	/* Reference:
	Books On-Line : Columns with the Name of an XPath Node Test
	http://technet.microsoft.com/en-us/library/bb522573.aspx
	*/
	SELECT
	ord AS "@ord", --tag attribute
	phonetype AS "@type", --tag attribute
	number AS "text()" --body of tag function see below
	FROM PhoneTable
	where
	Person.id = PhoneTable.personid
	FOR XML PATH('phonenumber'),ROOT('phonelist'), TYPE
	) AS calc(phonelist)
	FOR XML PATH('customer'), ROOT('bookofbusiness'), ELEMENTS XSINIL

view raw serialization_xml.sql hosted with ❤ by GitHub

	<!-- Result: -->
	<bookofbusiness xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
	<customer>
	<firstname>Bob</firstname>
	<lastname>Smith</lastname>
	<phonelist>
	<phonenumber ord="1" type="home">806-555-1234</phonenumber>
	<phonenumber ord="2" type="work">806-555-2843</phonenumber>
	</phonelist>
	</customer>
	<customer>
	<firstname>Jane</firstname>
	<lastname>Doe</lastname>
	<phonelist>
	<phonenumber ord="1" type="work">806-555-0282</phonenumber>
	<phonenumber ord="2" type="cell">806-555-9028</phonenumber>
	<phonenumber ord="3" type="home">806-555-2103</phonenumber>
	</phonelist>
	</customer>
	<customer>
	<firstname>John</firstname>
	<lastname>Jones</lastname>
	<phonelist xsi:nil="true"></phonelist>
	</customer>
	</bookofbusiness>

view raw serializeation_xml_result.xml hosted with ❤ by GitHub

Initial post

You are likely to find nothing profound here, just ill-advised tools that I have built while working on SQL Server and PostgreSQL.