Conversion D’une Variable XML À Une Table

 1
 2declare @xml xml = '
 3<MyRow rowValue2="NewValue3">
 4    <IdProduct>312345</IdProduct>
 5    <CodeProduct>CAPEX</CodeProduct>
 6    <IdCaract1>12</IdCaract1>
 7    <IdCaract2>23</IdCaract2>
 8    <Description>XML Support in Microsoft SQL Server</Description>
 9    <item>
10        <myitem>13</myitem>
11    </item>
12    <item>
13        <myitem>14</myitem>
14    </item>
15</MyRow>
16<MyRow rowValue1="myNewRow1" rowValue2="NewValue2">
17    <IdProduct CanBeDeleted="Yes">312345</IdProduct>
18    <CodeProduct>TOPEX</CodeProduct>
19    <IdCaract1>13</IdCaract1>
20    <IdCaract2>14</IdCaract2>
21    <Description>Server-Side Support</Description>
22</MyRow>
23'

1
2SELECT
3  Tbl.Col.value('IdProduct[1]', 'int') as IdProduct,
4  Tbl.Col.value('CodeProduct[1]', 'nvarchar(10)') as CodeProduct,
5  Tbl.Col.value('IdCaract1[1]', 'smallint') as IdCaract1,
6  Tbl.Col.value('IdCaract2[1]', 'smallint') as IdCaract2,
7  Tbl.Col.value('Description[1]', 'nvarchar(40)') as [Description]
8FROM   @xml.nodes('//MyRow') Tbl(Col);

1
2IdProduct   CodeProduct IdCaract1 IdCaract2 Description
3----------- ----------- --------- --------- ----------------------------------------
4312222      CAPEX       12        23        XML Support in Microsoft SQL Server
5312333      TOPEX       13        14        Server-Side Support

1
2SELECT
3  Tbl.Col.value('IdProduct[1]', 'int') as IdProduct,
4  Tbl.Col.value('@rowValue1', 'nvarchar(20)') as rowValue1,
5  Tbl.Col.value('@rowValue2', 'nvarchar(20)') as rowValue2
6FROM   @xml.nodes('//MyRow') Tbl(Col);

1
2IdProduct   rowValue1            rowValue2
3----------- -------------------- --------------------
4312222      NULL                 NewValue3
5312333      myNewRow1            NewValue2

1
2SELECT
3  Tbl.Col.value('../IdProduct[1]', 'int') as IdProduct,
4  Tbl.Col.value('myitem[1]', 'smallint') as item
5FROM   @xml.nodes('//MyRow/item') Tbl(Col);

1
2IdProduct   item
3----------- ------
4312222      13
5312222      14

 1
 2SELECT
 3  Tbl.Col.value('../@rowValue1', 'nvarchar(20)') as rowValue1,
 4  Tbl.Col.value('../@rowValue2', 'nvarchar(20)') as rowValue2,
 5  Tbl.Col.value('../IdProduct[1]', 'int') as IdProduct,
 6  Tbl.Col.value('../CodeProduct[1]', 'nvarchar(10)') as CodeProduct,
 7  Tbl.Col.value('../IdCaract1[1]', 'smallint') as IdCaract1,
 8  Tbl.Col.value('../IdCaract2[1]', 'smallint') as IdCaract2,
 9  Tbl.Col.value('../Description[1]', 'nvarchar(40)') as [Description],
10  Tbl.Col.value('myitem[1]', 'smallint') as item
11FROM   @xml.nodes('//MyRow/item') Tbl(Col);

1
2rowValue1            rowValue2            IdProduct   CodeProduct IdCaract1 IdCaract2 Description                              item
3-------------------- -------------------- ----------- ----------- --------- --------- ---------------------------------------- ------
4NULL                 NewValue3            312222      CAPEX       12        23        XML Support in Microsoft SQL Server      13
5NULL                 NewValue3            312222      CAPEX       12        23        XML Support in Microsoft SQL Server      14

 1
 2DECLARE @TABLE_NAME as NVARCHAR(100);
 3DECLARE @TABLE_SCHEMA as NVARCHAR(100);
 4DECLARE @MyElement as NVARCHAR(100);
 5DECLARE @MyFieldXml as NVARCHAR(100);
 6DECLARE @MyPrefix as NVARCHAR(100);
 7
 8
 9SET @TABLE_NAME = 'sqlwatch_config_sql_instance'
10SET @TABLE_SCHEMA = 'dbo'
11
12--MyFieldXml
13SET @MyElement  = 'msg_' + @TABLE_NAME; -- we must check if this name is ok for YOU.
14SET @MyFieldXml = 'Message';            -- we must check if this name is ok for YOU.
15SET @MyPrefix   = 'tbl.Col';            -- we must check if this name is ok for YOU.
16SET NOCOUNT ON
17
18BEGIN TRY DROP TABLE #TMP END TRY BEGIN CATCH END CATCH;
19SELECT
20        TABLE_SCHEMA
21       ,TABLE_NAME
22       ,ORDINAL_POSITION AS rownumber
23       ,COLUMN_NAME AS 'ColumnName'
24       ,DATA_TYPE + CASE WHEN CHARacter_maximum_length IS NOT NULL THEN '(' + CAST(CHARacter_maximum_length AS NVARCHAR(100)) + ')' ELSE '' END AS 'DataType'
25       ,IS_NULLABLE
26       ,'@'+REPLACE(COLUMN_NAME,' ','_') + ' AS ' + DATA_TYPE + CASE WHEN CHARacter_maximum_length IS NOT NULL THEN '(' + CAST(CHARacter_maximum_length AS NVARCHAR(100)) + ')' ELSE '' END as procedureParameter
27       , @MyPrefix+'.value(''('+COLUMN_NAME+')[1]'', '''+DATA_TYPE + CASE WHEN CHARacter_maximum_length IS NOT NULL THEN '(' + CAST(CHARacter_maximum_length AS NVARCHAR(100)) + ')' ELSE '' END+''') as ['+COLUMN_NAME+']' as xmlParameterV1
28       , '['+@MyFieldXml+'].value(''('+@MyElement+'/'+TABLE_NAME+'/'+COLUMN_NAME+')[1]'', '''+DATA_TYPE + CASE WHEN CHARacter_maximum_length IS NOT NULL THEN '(' + CAST(CHARacter_maximum_length AS NVARCHAR(100)) + ')' ELSE '' END+''') as ['+COLUMN_NAME+']' as xmlParameterV2
29       , 'DECLARE @'+REPLACE(COLUMN_NAME,' ','_') + ' AS ' + DATA_TYPE + CASE WHEN CHARacter_maximum_length IS NOT NULL THEN '(' + CAST(CHARacter_maximum_length AS NVARCHAR(100)) + ')' ELSE '' END + ';' AS [DeclareVariable]
30       , '['+COLUMN_NAME+'] ' + + DATA_TYPE + CASE WHEN CHARacter_maximum_length IS NOT NULL THEN '(' + CAST(CHARacter_maximum_length AS NVARCHAR(100)) + ')' ELSE '' END + CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE ' NULL' END  AS CreateField
31INTO #TMP
32FROM INFORMATION_SCHEMA.COLUMNS
33WHERE TABLE_SCHEMA = @TABLE_SCHEMA AND TABLE_NAME = @TABLE_NAME;
34
35DECLARE @SQL AS NVARCHAR(MAX);
36SET @SQL = '';
37
38SELECT @SQL = @SQL
39              + CASE WHEN @SQL = '' THEN '  ' ELSE ', ' END
40              + xmlParameterV1
41              + CHAR(13) + CHAR(10)
42FROM
43#TMP;
44
45
46PRINT 'DECLARE @xml as xml;
47SELECT @xml = (
48SELECT * FROM [' + @TABLE_SCHEMA + '].[' + @TABLE_NAME + '] for xml path
49);
50
51select @xml as [xml];
52'
53SET @MyPrefix = replace(@MyPrefix, '.','(')+')'
54SET @SQL = 'SELECT'+ CHAR(13) + CHAR(10)
55          + @SQL
56          + 'FROM   @xml.nodes(''row'') '+@MyPrefix + CHAR(13) + CHAR(10)
57 
58
59PRINT @SQL