Thanks Steven! I've been able to get almost everything formatted the way I want by using some of your examples.
Now I just need to append a separate <control> element to the bottom of the results of this query:
DECLARE @main TABLE ( [created-date-time] DATETIME, origin VARCHAR (5) ) -- need to fix date. remove tab char
INSERT INTO @main VALUES ( GETDATE(), 'IKA' )
SELECT [created-date-time] AS [@created-date-time], origin AS [@origin],
(SELECT TOP 2 id_type AS [@id-type], id AS [@id], seq AS [@seq],
(SELECT [SERVICE] AS [@service], country AS [@country],[action] AS [@action],
(SELECT UPPER([Username])
FOR XML PATH('vendor-account-id'), TYPE),
(SELECT [status] AS [customer-status]
FOR XML PATH(''), TYPE),
(SELECT '' AS 'credit-decision'
FOR XML PATH(''), TYPE),
(SELECT [TeamID] AS 'team-id'
FOR XML PATH(''), TYPE),
(SELECT [FirstName] AS 'first', [MiddleName] AS 'middle', [LastName] AS 'last', [suffix]
FOR XML PATH('name'), TYPE),
(SELECT 'en' AS 'language'
FOR XML PATH(''), TYPE),
(SELECT currency
FOR XML PATH(''), TYPE),
(SELECT '' AS 'ssn'
FOR XML PATH(''), TYPE),
(SELECT [loa_date] AS [startdate]-- need to fix date. remove tab char
FOR XML PATH(''), TYPE),
(SELECT [street], '' AS [apt], '' AS [double-locality], [city], [state], [country], rtrim([PostalCode]) AS 'postal-code'
FOR XML PATH('adress'), TYPE),
(SELECT phone
FOR XML PATH(''), TYPE),
(SELECT '' AS 'alternate-phone'
FOR XML PATH(''), TYPE),
(SELECT 'Y' AS 'residential'
FOR XML PATH(''), TYPE),
(SELECT 'ONLINE' AS 'entered-by'
FOR XML PATH(''), TYPE),
(SELECT [loa_date]-- need to fix date. remove tab char
FOR XML PATH(''), TYPE),
(SELECT [rate_plan] AS 'rate-plan' -- might need to be 2-digit eg... 09 not 9
FOR XML PATH(''), TYPE),
(SELECT '' AS 'enrollment-method'
FOR XML PATH(''), TYPE),
(SELECT '' AS 'rep-verified'
FOR XML PATH(''), TYPE),
(SELECT '' AS 'account-number'
FOR XML PATH(''), TYPE),
(SELECT '' AS 'business-name'
FOR XML PATH(''), TYPE),
(SELECT [reason_code] AS [reason-code]
FOR XML PATH(''), TYPE),
(SELECT '' AS 'is-rep'
FOR XML PATH(''), TYPE)
FOR XML PATH('customer'), TYPE
)
FROM #acnUpdates
FOR XML PATH ('transaction'), type--ROOT ('vip-customer-load')
)
FOR XML PATH('data'), TYPE
)
FROM @main m
FOR XML PATH('vip-customer-load'), TYPE
Gives me these results:
<vip-customer-load created-DATE-TIME="2013-07-11T23:14:28.053" origin="IKA">
<data>
<transaction id-TYPE="VENDOR-CUSTOMER" id="adsfas722" seq="1">
<customer service="ISP" country="CA" action="INACTIVE">
<vendor-account-id>SQLTEST_Gdfadsfsd</vendor-account-id>
<customer-status>CHANGE</customer-status>
<credit-decision />
<team-id>asdfsad7104</team-id>
<name>
<first>dsfadsf</first>
<middle />
<last>adsfasd</last>
<suffix />
</name>
<language>en</language>
<currency>CAD</currency>
<ssn />
<startdate>2010-05-19T00:00:00</startdate>
<adress>
<street>1762 Du Lac </street>
<apt />
<double-locality />
<city>dsafasd</city>
<state>QC</state>
<country>CA</country>
<postal-code>fdsa</postal-code>
</adress>
<phone>dsafasd29434/phone>
<alternate-phone />
<residential>Y</residential>
<entered-by>ONLINE</entered-by>
<loa-date>2010-05-19T00:00:00</loa-date>
<rate-plan>9</rate-plan>
<enrollment-method />
<rep-verified />
<account-number />
<business-name />
<reason-code>70,71</reason-code>
<is-rep />
</customer>
</transaction>
<transaction id-TYPE="VENDOR-CUSTOMER" id="27dfs" seq="2">
<customer service="ISP" country="CA" action="INACTIVE">
<vendor-account-id>SQLTdfasfasd</vendor-account-id>
<customer-status>CHANGE</customer-status>
<credit-decision />
<team-id>fadsfasd</team-id>
<name>
<first>fadsdf</first>
<middle />
<last>adsfadf</last>
<suffix />
</name>
<language>en</language>
<currency>CAD</currency>
<ssn />
<startdate>2010-05-19T00:00:00</startdate>
<adress>
<street>176dfasf</street>
<apt />
<double-locality />
<city>Clarenfdasfc/city>
<state>QC</state>
<country>CA</country>
<postal-code>j0j1fdasb0</postal-code>
</adress>
<phone>4502943fasdfsafa440</phone>
<alternate-phone />
<residential>Y</residential>
<entered-by>ONLINE</entered-by>
<loa-date>2010-05-19T00:00:00</loa-date>
<rate-plan>9</rate-plan>
<enrollment-method />
<rep-verified />
<account-number />
<business-name />
<reason-code>70,71</reason-code>
<is-rep />
</customer>
</transaction>
</data>
</vip-customer-load>
Now I need to append the following element to the above results...
<control>
<customer service="ISP" country="CA">
<transaction-count>4</transaction-count>
<add-count>1</add-count>
<change-count>3</change-count>
<delete-count>0</delete-count>
<vendor-system-total-count>9082</vendor-system-total-count>
<vendor-system-active-count>150</vendor-system-active-count>
<vendor-system-incomplete-count>0</vendor-system-incomplete-count>
</customer>
</control>
Thanks...!