MSSQL tsql xml shaping

serialization_xml.sql

/*
 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 

serializeation_xml_result.xml

<!-- 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>