Building nice XML from SQL Server Tables

The XML functionality of SQL Server is vast, however, the best subset of that functionality that I've found may be demonstrated in the following example:

Unfortunately, the example result is listed before the code.

• Join all the desired tables together
• Set the desired root element
• Set the path to an empty string to avoid an extra XML element at the root of each resulting row
• Provide an XPATH name with attributes for each of the desired columns (select * doesn't work well)
• Keep all the selected columns together by XPATH name or you'll split your xml attributes

See my next blog entry for my best way to shred the xml

	<Students>
	<Student PersonalTitlePrefix="Mrs" FirstName="Shelby" MiddleName="Ruby" LastSurname="Barajas" SexTypeId="1" BirthDate="2005-12-15" HispanicLatinoEthnicity="1" OldEthnicityTypeId="1" LoginId="sbarajas" StudentUniqueId="605704" Id="1322C606-D644-497B-8548-207933921855">
	<Address AddressTypeId="10" StreetNumberName="654 Mission Hills" City="Grand Bend" StateAbbreviationTypeId="54" PostalCode="78834" NameOfCounty="WILLISTON" />
	</Student>
	<Student PersonalTitlePrefix="Mrs" FirstName="Sarah" LastSurname="Stevens" SexTypeId="1" BirthDate="2005-06-12" HispanicLatinoEthnicity="0" OldEthnicityTypeId="1" LoginId="sstevens" StudentUniqueId="555555" Id="9F5B105B-0B03-4508-B7B4-AD1731A85B6E">
	<Address AddressTypeId="3" StreetNumberName="123 Simons Way" City="Grand Bend" StateAbbreviationTypeId="54" PostalCode="78834" NameOfCounty="WILLISTON" />
	</Student>
	<Student PersonalTitlePrefix="Mr" FirstName="Gilbert" MiddleName="Jesse" LastSurname="Fritz" SexTypeId="2" BirthDate="2005-12-16" HispanicLatinoEthnicity="1" OldEthnicityTypeId="4" LoginId="gfritz" StudentUniqueId="604888" Id="5757B9B9-C4AD-4B6D-B1A0-056B2D08B7BD">
	<Address AddressTypeId="3" StreetNumberName="16 Green Hague Way" City="Grand Bend" StateAbbreviationTypeId="54" PostalCode="78834" NameOfCounty="WILLISTON" />
	</Student>
	<Student PersonalTitlePrefix="Mrs" FirstName="Debbie" LastSurname="Savage" SexTypeId="1" BirthDate="2005-06-12" HispanicLatinoEthnicity="0" OldEthnicityTypeId="1" LoginId="dsavage" StudentUniqueId="605372" Id="DE2FF0BA-F23B-4323-87C9-FA4AF3C3FA44">
	<Address AddressTypeId="3" StreetNumberName="123 Simons Way" City="Grand Bend" StateAbbreviationTypeId="54" PostalCode="78834" NameOfCounty="WILLISTON" />
	</Student>
	<Student PersonalTitlePrefix="Mr" FirstName="Sandro" MiddleName="Francisco" LastSurname="Miguel" SexTypeId="2" BirthDate="2005-12-16" HispanicLatinoEthnicity="1" OldEthnicityTypeId="4" LoginId="smiguel" StudentUniqueId="777777" Id="AFE73075-5460-4FBD-858B-E8864E8E1A38">
	<Address AddressTypeId="3" StreetNumberName="16 Green Hague Way" City="Grand Bend" StateAbbreviationTypeId="54" PostalCode="78834" NameOfCounty="WILLISTON" />
	</Student>
	<Student PersonalTitlePrefix="Ms" FirstName="Alicia" MiddleName="Angela" LastSurname="Dennis" SexTypeId="1" BirthDate="2005-11-09" HispanicLatinoEthnicity="1" OldEthnicityTypeId="4" LoginId="adennis" StudentUniqueId="604903" Id="47E92DBE-AE26-4F52-B532-5F8BD9E02826">
	<Address AddressTypeId="3" StreetNumberName="25 North Clarendon Boulevard" City="Grand Bend" StateAbbreviationTypeId="54" PostalCode="78834" NameOfCounty="WILLISTON" />
	<Language LanguageDescriptorId="524">
	<LanguageUse LanguageUseTypeId="1" />
	</Language>
	</Student>
	</Students>

view raw Results.xml hosted with ❤ by GitHub

	DECLARE @xmlDocument XML
	SET @xmlDocument = (select
	PersonalTitlePrefix AS "Student/@PersonalTitlePrefix",
	FirstName AS "Student/@FirstName",
	MiddleName AS "Student/@MiddleName",
	LastSurname AS "Student/@LastSurname",
	SexTypeId AS "Student/@SexTypeId",
	BirthDate AS "Student/@BirthDate",
	HispanicLatinoEthnicity AS "Student/@HispanicLatinoEthnicity",
	OldEthnicityTypeId AS "Student/@OldEthnicityTypeId",
	LoginId AS "Student/@LoginId",
	StudentUniqueId AS "Student/@StudentUniqueId",
	Id AS "Student/@Id",
	AddressTypeId AS "Student/Address/@AddressTypeId",
	StreetNumberName AS "Student/Address/@StreetNumberName",
	City AS "Student/Address/@City",
	StateAbbreviationTypeId AS "Student/Address/@StateAbbreviationTypeId",
	PostalCode AS "Student/Address/@PostalCode",
	NameOfCounty AS "Student/Address/@NameOfCounty",
	T2.LanguageDescriptorId AS "Student/Language/@LanguageDescriptorId",
	LanguageUseTypeId AS "Student/Language/LanguageUse/@LanguageUseTypeId"
	from [t_edfi].[Student] T
	left join [t_edfi].[StudentAddress] T1 on T.tid_Student = T1.tid_Student
	left join [t_edfi].[StudentLanguage] T2 on T.tid_Student = T2.tid_Student
	left join [t_edfi].[StudentLanguageUse] T3 on T2.tid_StudentLanguage = T3.tid_StudentLanguage
	for xml PATH(''), TYPE, ROOT('Students'))
	select @xmlDocument

view raw TablesToXML.sql hosted with ❤ by GitHub