Did you get this resolved? It only takes a minor change from previously posted code

😎

SELECT

AC.name AS 'Acct',

(SELECT column_id AS 'Item/data()'

FROM sys.columns AS ITEMS1

WHERE ITEMS1.object_id = AC.object_id

FOR XML PATH ('Items'), TYPE)

FROM

sys.tables AC WITH(NOLOCK)

FOR XML PATH ('Account'), ROOT ('Accts'), ELEMENTS

Results

<Accts>

<Account>

<Acct>#A161554C</Acct>

<Items>

<Item>1</Item>

</Items>

<Items>

<Item>2</Item>

</Items>

<Items>

<Item>3</Item>

</Items>

<Items>

<Item>4</Item>

</Items>

<Items>

<Item>5</Item>

</Items>

<Items>

<Item>6</Item>

</Items>

<Items>

<Item>7</Item>

</Items>

<Items>

<Item>8</Item>

</Items>

</Account>