Convert XML to Table and Table to XML

 1
 2USE [NORTHWND]
 3
 4GO
 5
 6SET ANSI_NULLS ON
 7
 8SET QUOTED_IDENTIFIER ON
 9
10GO
11
12BEGIN TRY DROP TABLE [dbo].[CustomersXML]; END TRY BEGIN CATCH END CATCH;
13
14CREATE TABLE [dbo].[CustomersXML](
15
16       [CustomersXMLID] [int] IDENTITY(1,1) NOT NULL,
17
18       [XMLCode] [xml] NULL,
19
20 CONSTRAINT [PK_CustomersXML] PRIMARY KEY CLUSTERED
21
22(
23
24       [CustomersXMLID] ASC
25
26)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
27
28) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
29
30GO

 1
 2---------------------------------------------------------
 3
 4-- Example 1 Query -- >> XML
 5
 6-- Attention : this example use "for xml auto, ELEMENTS, root('TableData')"
 7
 8/*
 9
10<TableData>
11
12  <Customer>
13
14    <RowNumber>1</RowNumber>
15
16    <CustomerID>ALFKI</CustomerID>
17
18    <ContactName>Maria Anders</ContactName>
19
20    <CompanyName>Alfreds Futterkiste</CompanyName>
21
22    <City>Berlin</City>
23
24  </Customer>
25
26</TableData>
27
28*/
29
30TRUNCATE TABLE [CustomersXML];
31
32DECLARE @RowNumber int;
33
34BEGIN TRY DROP TABLE #tmp; END TRY BEGIN CATCH END CATCH;
35
36SELECT ROW_NUMBER() over (ORDER BY CustomerID) as RowNumber, CustomerID,ContactName,CompanyName,City
37
38INTO #tmp
39
40FROM Customers
41
42-- FOR XML AUTO--, ELEMENTS--, XMLSCHEMA;
43
44DECLARE @xml xml;
45
46WHILE (exists(select * from #tmp))
47
48BEGIN
49
50       SELECT TOP 1 @RowNumber=RowNumber FROM #tmp;
51
52       SELECT @xml = (
53
54                     SELECT RowNumber, CustomerID, ContactName, CompanyName, City
55
56                     FROM #tmp as Customer  WHERE @RowNumber=RowNumber FOR XML AUTO, elements, root('TableData')-- AUTO,
57
58                     )
59
60       INSERT INTO [CustomersXML](XMLCode) VALUES (@xml);
61
62       DELETE FROM #tmp WHERE @RowNumber=RowNumber;
63
64END
65
66SELECT top 1 * FROM [dbo].[CustomersXML];

 1
 2-- Method 1
 3
 4SELECT
 5
 6       [XMLCode].value('(/TableData/Customer/RowNumber)[1]', 'int') as RowNumber,
 7
 8       [XMLCode].value('(/TableData/Customer/CustomerID)[1]', 'nvarchar(max)') as CustomerID,
 9
10       [XMLCode].value('(/TableData/Customer/ContactName)[1]', 'nvarchar(max)') as ContactName,
11
12       [XMLCode].value('(/TableData/Customer/CompanyName)[1]', 'nvarchar(max)') as CompanyName,
13
14       [XMLCode].value('(/TableData/Customer/City)[1]', 'nvarchar(max)')  as City
15
16FROM   [CustomersXML]
17
18WHERE [XMLCode].exist('/TableData/Customer') = 1

 1
 2-- Method 2
 3
 4SELECT
 5
 6        x.m.value('(RowNumber)[1]', 'int') as RowNumber,
 7
 8        x.m.value('(CustomerID)[1]', 'nvarchar(max)') as CustomerID,
 9
10        x.m.value('(ContactName)[1]', 'nvarchar(max)') as ContactName,
11
12        x.m.value('(CompanyName)[1]', 'nvarchar(max)') as CompanyName,
13
14        x.m.value('(City)[1]', 'nvarchar(max)')  as City
15
16FROM   [CustomersXML] xt
17
18cross apply xt.XMLCode.nodes('TableData/Customer') x(m)

 1
 2-- Example 2 Query -- >> XML
 3
 4-- Attention : this example use "for xml auto, root('TableData')"
 5
 6/*
 7
 8<TableData>
 9
10  <Customer RowNumber="1" CustomerID="ALFKI" ContactName="Maria Anders" CompanyName="Alfreds Futterkiste" City="Berlin" />
11
12</TableData>
13
14*/
15
16TRUNCATE TABLE [CustomersXML];
17
18DECLARE @RowNumber int;
19
20BEGIN TRY DROP TABLE #tmp; END TRY BEGIN CATCH END CATCH;
21
22SELECT ROW_NUMBER() over (ORDER BY CustomerID) as RowNumber, CustomerID,ContactName,CompanyName,City
23
24INTO #tmp
25
26FROM Customers
27
28-- FOR XML AUTO--, ELEMENTS--, XMLSCHEMA;
29
30DECLARE @xml xml;
31
32WHILE (exists(select * from #tmp))
33
34BEGIN
35
36       SELECT TOP 1 @RowNumber=RowNumber FROM #tmp;
37
38       SELECT @xml = (
39
40                     SELECT RowNumber, CustomerID, ContactName, CompanyName, City
41
42                     FROM #tmp as Customer  WHERE @RowNumber=RowNumber FOR XML AUTO, root('TableData')-- AUTO,
43
44                     )
45
46       INSERT INTO [CustomersXML](XMLCode) VALUES (@xml);
47
48       DELETE FROM #tmp WHERE @RowNumber=RowNumber;
49
50END
51
52SELECT top 1 * FROM [dbo].[CustomersXML];

 1
 2-- Sample 2 Query << -- XML
 3
 4SELECT top 1 * FROM [dbo].[CustomersXML];
 5
 6-- Method 1
 7
 8SELECT
 9
10       [XMLCode].value('(/TableData/Customer/@RowNumber)[1]', 'int') as RowNumber,
11
12       [XMLCode].value('(/TableData/Customer/@CustomerID)[1]', 'nvarchar(max)') as CustomerID,
13
14       [XMLCode].value('(/TableData/Customer/@ContactName)[1]', 'nvarchar(max)') as ContactName,
15
16       [XMLCode].value('(/TableData/Customer/@CompanyName)[1]', 'nvarchar(max)') as CompanyName,
17
18       [XMLCode].value('(/TableData/Customer/@City)[1]', 'nvarchar(max)')  as City
19
20FROM   [CustomersXML]
21
22-- WHERE [XMLCode].exist('/TableData/Customer') = 1  -- Just to make sure

 1
 2-- Method 2
 3
 4SELECT
 5
 6       x.m.value('@RowNumber[1]', 'int') as RowNumber,
 7
 8       x.m.value('@CustomerID[1]', 'nvarchar(max)') as CustomerID,
 9
10       x.m.value('@ContactName[1]', 'nvarchar(max)') as ContactName,
11
12       x.m.value('@CompanyName[1]', 'nvarchar(max)') as CompanyName,
13
14       x.m.value('@City[1]', 'nvarchar(max)')  as City
15
16FROM   [CustomersXML] xt
17
18cross apply xt.XMLCode.nodes('TableData/Customer') x(m)