If it's possible to add a little code to the current process, you might be able to cheat a little.
--Demo setup
DROP TABLE IF EXISTS [dbo].[CUSTOMER]
CREATE TABLE [dbo].[CUSTOMER](
[CustomerID] [int] NOT NULL,
[FirstName] [varchar](20) NOT NULL,
[LastName] [varchar](20) NOT NULL,
[Email] [varchar](30) NOT NULL,
[PhoneNo] [int] NOT NULL,
[StreetAddress] [varchar](40) NULL,
[City] [varchar](20) NULL,
[OrderID] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [Test]
GO
INSERT [dbo].[CUSTOMER] ([CustomerID], [FirstName], [LastName], [Email], [PhoneNo], [StreetAddress], [City], [OrderID]) VALUES (13579246, N'David', N'Casey', N'dcasey@gmail.com', 12826363, N'34 Bray Rd', N'Dublin', 94724274)
GO
INSERT [dbo].[CUSTOMER] ([CustomerID], [FirstName], [LastName], [Email], [PhoneNo], [StreetAddress], [City], [OrderID]) VALUES (16579946, N'Donal', N'Smith', N'dsmith@gmail.com', 13826563, N'78 Ballybrack Rd', N'Dublin', 14724274)
GO
INSERT [dbo].[CUSTOMER] ([CustomerID], [FirstName], [LastName], [Email], [PhoneNo], [StreetAddress], [City], [OrderID]) VALUES (17579946, N'Steve', N'Reidy', N'sreidy@gmail.com', 13827563, N'22 Conna Rd', N'Cork', 64724274)
GO
INSERT [dbo].[CUSTOMER] ([CustomerID], [FirstName], [LastName], [Email], [PhoneNo], [StreetAddress], [City], [OrderID]) VALUES (18579946, N'Michael', N'Yule', N'myule@gmail.com', 14827563, N'44 Blasket Rd', N'Kilkenny', 44424274)
GO
Outside of any transaction, create a new table to hold some XML
drop table if exists XmlDataTable
CREATE TABLE XmlDataTable (XmlDataColumn xml)
Now, in your transaction, create your global temp table and select from it converting to xml raw. Then insert that value into XmlDataTable
.
begin transaction
drop table if exists ##temp
SELECT * INTO ##temp FROM customer --This is your normal create for the global temp table
DECLARE @XmlData XML
--select the data from the global temp table as xml raw
SET @XmlData = (SELECT * FROM ##temp FOR XML raw)
--insert that value into LargeVarcharTable
INSERT INTO XmlDataTable VALUES (@XmlData)
--rollback
In another session, you should be able to select * from xmldatatable (nolock)
to select the raw xml.
select * from xmldatatable (nolock)
<row CustomerID="13579246" FirstName="David" LastName="Casey" Email="dcasey@gmail.com" PhoneNo="12826363" StreetAddress="34 Bray Rd" City="Dublin" OrderID="94724274"/>
<row CustomerID="16579946" FirstName="Donal" LastName="Smith" Email="dsmith@gmail.com" PhoneNo="13826563" StreetAddress="78 Ballybrack Rd" City="Dublin" OrderID="14724274"/>
<row CustomerID="17579946" FirstName="Steve" LastName="Reidy" Email="sreidy@gmail.com" PhoneNo="13827563" StreetAddress="22 Conna Rd" City="Cork" OrderID="64724274"/>
<row CustomerID="18579946" FirstName="Michael" LastName="Yule" Email="myule@gmail.com" PhoneNo="14827563" StreetAddress="44 Blasket Rd" City="Kilkenny" OrderID="44424274"/>