#sql #sql-server #tsql #sql-server-2017 #dynamicquery
#sql #sql-сервер #tsql #sql-server-2017 #dynamicquery
Вопрос:
У меня есть xml, который предоставляет мне имя таблицы с соответствующим идентификатором столбца и столбца для обновления или вставки на основе этих идентификаторов ниже приведен xml:
<tables>
<table>
<name>Table1</name>
<attr>
<id>1</id>
<columns>
<col_name>col1</col_name>
<col_val>123</col_val>
<columns>
<columns>
<col_name>col2</col_name>
<col_val>345</col_val>
<columns>
</attr>
<attr>
<id>2</id>
<columns>
<col_name>col3</col_name>
<col_val>123</col_val>
<columns>
</attr>
<attr>
<id>4</id>
<columns>
<col_name>col2</col_name>
<col_val>123</col_val>
<columns>
</attr>
</table>
<table>
<name>Table2</name>
<attr>
<id>1</id>
<columns>
<col_name>coltb1</col_name>
<col_val>123</col_val>
<columns>
<columns>
<col_name>coltb3</col_name>
<col_val>345</col_val>
<columns>
</attr>
<attr>
<id>3</id>
<columns>
<col_name>coltb4</col_name>
<col_val>123</col_val>
<columns>
</attr>
</table>
</tables>
В этом у меня есть имя таблицы, которое я могу сопоставить с таблицей, созданной в моей БД, и на основе столбца ID я должен проверить, присутствует ли идентификатор или нет, если присутствует, тогда я должен обновить столбцы, которые присутствуют в узле столбца, значением, а если нет, я должен обновить столбцы, которые присутствуют в узле столбца.необходимо вставить в таблицу.
Ниже приведен код, который я сделал до сих пор,
;WITH CTE (ID,TableName) As (SELECT ROW_NUMBER() OVER (ORDER BY CAST(y.item.query('data(name)') AS NVARCHAR(300))) AS ROWNUM,
CAST(y.item.query('data(name)') AS NVARCHAR(300)) AS TableName
FROM @input.nodes('/tables/table') y(item))
SELECT ID,TableName into #tmp FROM CTE
DECLARE @Counter INT,@tableName nvarchar(300)
SET @Counter=1
WHILE (@Counter<=(SELECT Count(*) FROM #tmp))
BEGIN
SET @tableName=(SELECT TableName FROM #tmp WHERE ID=@Counter)
;WITH CTE2(id) AS (SELECT CAST(x.item.query('data(id)') AS NVARCHAR(30)) AS id
FROM @input.nodes('/tables/table') y(item)
CROSS APPLY y.item.nodes('./attr') x(item)
WHERE CAST(y.item.query('data(name)') AS NVARCHAR(300))=@tableName)
SELECT * FROM CTE2
SET @Counter=@Counter 1
END
Здесь я могу получить имя таблицы, и при ее циклическом воспроизведении я также получаю идентификатор, но я не могу найти имя столбца и не понимаю, как выполнить слияние с этим именем столбца, поскольку оно все динамическое.
Таблицы, которые у меня есть в моей БД, следующие
Table1:
ID|col1|col2|col3|col4|col5
---------------------------
1 |123 |345 |456 |null|89
2 |222 |444 |667 |890 |99
Таблица2
ID|coltb1|coltb2|coltb3|coltb4|coltb5
------------------------------------
1 |786 |678 |880 |99 |788
2 |345 |678 |667 |9990 |008
3 |344 |667 |623 |945 |678
Комментарии:
1. Вам придется использовать динамический SQL, однако, если это требование звучит более вероятно, что у вас есть недостаток дизайна.
2. не могли бы вы подсказать мне, что еще я мог бы сделать, подготовка XML выполняется мной только его json, который я конвертирую в xml
3. Если вы получаете JSON, почему бы не использовать его как JSON? Преобразование его в XML кажется бессмысленной задачей, когда SQL Server может читать JSON.
4. на самом деле в json есть ненужные значения, которыми мне нужно манипулировать в node и преобразовывать их в xml, кроме того, я не так много знаю о синтаксическом анализе sql в формате json
5. Тогда я подозреваю, что вы не рассказываете нам всю историю, наряду с недостатком дизайна.
Ответ №1:
Вы можете использовать следующее в качестве основы:
DECLARE @XML XML = N'<tables><table><name>Table1</name><attr><id>1</id><columns><col_name>col1</col_name><col_val>123</col_val></columns><columns><col_name>col2</col_name><col_val>345</col_val></columns></attr><attr><id>2</id><columns><col_name>col3</col_name><col_val>123</col_val></columns></attr><attr><id>4</id><columns><col_name>col2</col_name><col_val>123</col_val></columns></attr></table><table><name>Table2</name><attr><id>1</id><columns><col_name>coltb1</col_name><col_val>123</col_val></columns><columns><col_name>coltb3</col_name><col_val>345</col_val></columns></attr><attr><id>3</id><columns><col_name>coltb4</col_name><col_val>123</col_val></columns></attr></table></tables>';
DROP TABLE IF EXISTS #DataSource;
CREATE TABLE #DataSource
(
[table_name] SYSNAME
,[id] INT
,[column_name] SYSNAME
,[column_value] INT
);
WITH DataSource ([table_name], [columns_xml]) AS
(
SELECT T.c.value('./name[1]', 'NVARCHAR(128)')
,T.c.query('./attr')
FROM @XML.nodes('/tables/table') T(c)
)
INSERT INTO #DataSource ([table_name], [id], [column_name], [column_value])
SELECT [table_name]
,T.c.value('(./id)[1]', 'INT')
,T.c.value('(./columns/col_name)[1]', 'VARCHAR(12)')
,T.c.value('(./columns/col_val)[1]', 'INT')
FROM DataSource
CROSS APPLY [columns_xml].nodes('./attr') T(c);
SELECT *
FROM #DataSource
DECLARE @current_table_name SYSNAME
,@current_columns VARCHAR(MAX)
,@current_columns_updated VARCHAR(MAX)
,@DynamicTSQLStatement NVARCHAR(MAX);
WHILE EXISTS(SELECT 1 FROM #DataSource)
BEGIN;
SELECT TOP 1 @current_table_name = [table_name]
FROM #DataSource;
SELECT @current_columns = STRING_AGG(QUOTENAME([column_name]), ',') WITHIN GROUP (ORDER BY [column_name] ASC)
,@current_columns_updated = STRING_AGG(QUOTENAME([column_name]) ' = ISNULL(S.' QUOTENAME([column_name]) ', T.' QUOTENAME([column_name]) ')', ',') WITHIN GROUP (ORDER BY [column_name] ASC)
FROM #DataSource
WHERE [table_name] = @current_table_name;
SET @DynamicTSQLStatement = N'
WITH DataSource AS
(
SELECT *
FROM
(
SELECT [id]
,[column_name]
,[column_value]
FROM #DataSource
WHERE [table_name] = ''' @current_table_name '''
) DS
PIVOT
(
MAX([column_value]) FOR [column_name] IN (' @current_columns ')
) PVT
)
MERGE ' @current_table_name ' AS T
USING DataSource AS S
ON T.[id] = S.[id]
WHEN MATCHED THEN
UPDATE SET ' @current_columns_updated '
WHEN NOT MATCHED THEN
INSERT ([id], ' @current_columns ')
VALUES (S.[id], ' @current_columns ');';
EXEC sp_executesql @DynamicTSQLStatement;
DELETE FROM #DataSource
WHERE [table_name] = @current_table_name;
END;