SQL XML Output

-- create some users
CREATE TABLE ##users (Id INT, firstName VARCHAR(50), lastName VARCHAR(50), residentState VARCHAR(2), userName VARCHAR(25));
INSERT INTO ##users (Id, firstName, lastName, residentState, userName)
VALUES
    (1, 'Jack', 'Smith', 'TX', 'jjSmith')
    , (2, 'Elvis', 'Presley', 'LA', 'the_king')
    , (3, 'Jack', 'Johnson', 'FL', 'some_dude')
    , (4, 'Bruce', 'Willis', 'NC', 'actionMan')
    , (5, 'Arnold', 'Kinston', 'ND', 'whos_this')

-- create some phone numbers for the users
CREATE TABLE ##phone (phoneId INT, userId INT, phoneType VARCHAR(10), phoneNumber VARCHAR(15));
INSERT INTO ##phone (phoneId, userId, phoneType, phoneNumber)
VALUES
    (1, 1, 'home', '111-222-3333')
    , (2, 1, 'cell', '222-333-4444')
    , (3, 1, 'work', '333-444-5555')
    , (4, 2, 'home', '444-555-6666')
    , (5, 2, 'work', '555-666-7777')
    , (6, 4, 'home', '666-777-8888')
    , (7, 5, 'home', '777-888-9999')

-- create some email addresses for the users
CREATE TABLE ##email (emailId INT, userId INT, emailType VARCHAR(10), emailAddress VARCHAR(100));
INSERT INTO ##email (emailId, userId, emailType, emailAddress)
VALUES
    (1, 2, 'home', '[email protected]')
    , (2, 2, 'work', '[email protected]')
    , (3, 3, 'home', '[email protected]')
    , (4, 4, 'home', '[email protected]')
    , (5, 5, 'home', '[email protected]')
    , (6, 5, 'work', '[email protected]')
    , (7, 5, 'disposable', '[email protected]')

-- raw example where everything in the row goes in as attributes with a default name of ROW, you can specify the name though
SELECT
    u.Id
    , u.firstName
    , u.lastName
    , u.residentState
    , u.userName
FROM ##users u
FOR XML RAW('user')

-- you can also give it a root element
SELECT
    u.Id
    , u.firstName
    , u.lastName
    , u.residentState
    , u.userName
FROM ##users u
FOR XML RAW, ROOT('users')

-- basic example with attributes, '@varchar' alias declaration with @ makes the column an attribute, all attributes declarations must come before data elements
SELECT
    u.Id AS '@userId'
    , u.firstName AS '@firstName'
    , u.lastName  AS '@lastName'
    , u.residentState
    , u.userName
FROM ##users u
FOR XML PATH('User'), TYPE, ROOT('Users')

-- slightly complex nested XML data
SELECT
    u.Id AS '@userId'
    , u.firstName AS '@firstName'
    , u.lastName  AS '@lastName'
    , u.residentState
    , u.userName
    , (
        SELECT p.phoneType AS 'PhoneNumber/@Type', p.phoneId AS 'PhoneNumber/@PhoneId', p.phoneNumber AS PhoneNumber
        FROM ##phone p
        WHERE p.userId = u.Id
        FOR XML PATH(''), TYPE, ROOT('PhoneNumbers')
    )
    , (
        SELECT e.emailId AS 'Email/@EmailId', e.emailAddress AS 'Email/@EmailAddress'
        FROM ##email e
        WHERE e.userId = u.Id
        FOR XML PATH(''), TYPE
    )
FROM ##users u
FOR XML PATH('User'), TYPE, ROOT('Users')

-- clean up
DROP TABLE ##users
DROP TABLE ##email
DROP TABLE ##phone