Блог

когда мы загружаем xml в cursor, мы указываем имя столбца и их тип данных и размер. вместо того, чтобы указывать вручную, как я мог бы сделать эту область динамической. предположим, что мой tsql выглядит следующим образом

 Exec sp_xml_preparedocument @XMLFormat OUTPUT, @DetailXml

-- Create Cursor from XML Table

Declare CurDetailRecord
Cursor For
Select productid,unit,rate,qty,amount
From Openxml (@XMLFormat, '/NewDataSet/PurchaseOrderDetail', 2)
With
(
productid Varchar(10),
unit Varchar(50),
rate decimal(18,2),
qty decimal(18,3),
amount decimal(18,2)
)
  

вот в качестве примера

 productid Varchar(10),
unit Varchar(50) 
  

и т.д. Я указываю, а также указываю тип и размер их данных.

итак, как я мог бы динамически создавать эту область и динамически извлекать имя столбца, тип и размер данных.

пожалуйста, направьте меня, спасибо.

Вы можете получить имена столбцов (которые являются узлами внутри узла PurchasePrderDetail) следующим образом:

  declare @xml xml='<NewDataSet><PurchaseOrderDetail>
<productid>19125</productid>
</PurchaseOrderDetail></NewDataSet>'
SELECT b.value('local-name(.)','nvarchar(128)')ColumnName,
    LEN(b.value('.','nvarchar(128)'))MaxLength
 FROM @xml.nodes('/NewDataSet/PurchaseOrderDetail/*') a(b)
  

Таким образом, вы можете сгенерировать динамическую инструкцию SQL для создания курсора с соответствующими именами столбцов и длиной, такими как varchar(MaxLength).

Но вы не можете получить типы данных из XML, не зная реальных имен столбцов, потому что данные в xml — это просто текст, и, например, «5» может быть int типом, а также просто текстом.

Редактировать

Если вы знаете имя таблицы, вы можете создать динамическую инструкцию SQL, используя метаданные из этой таблицы, используя это:

 ; With cols as(
SELECT COLUMN_NAME,
UPPER(DATA_type)
 
case when data_type like  '%char' then 
    case when CHARACTER_MAXIMUM_LENGTH=-1 THEN ' (MAX)'
    else ' (' CAST(CHARACTER_MAXIMUM_LENGTH as nvarchar) ')'
    END
ELSE '' 
END ColConv
 FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='PurchaseOrderDetail'),
XMLS as(
SELECT b.value('local-name(.)','nvarchar(128)')ColumnName,
    b.value('.','nvarchar(128)')Value
 FROM @xml.nodes('/NewDataSet/PurchaseOrderDetail/*') a(b)
)
SELECT XMLS.ColumnName,'CAST (''' XMLS.Value ''' AS '  ColConv ''')'  FROM XMLS
JOIN cols ON XMLS.ColumnName=cols.COLUMN_NAME
  

В качестве выходных данных у вас будет имя столбца и значение с соответствующим CAST предложением. Затем вы можете создать динамический оператор, который вам нужен.

Обычно информация о типах данных и именах полей описывается в XSD файле (определение XML-схемы).

Итак, вам нужно иметь действительный XSD-файл для каждого вашего XML-файла, тогда вы сможете получить имя полей и тип данных.

Вот ссылка для лучшего понимания XSD

И вот как шаг за шагом работать с XSD и XML

Надеюсь, это поможет вам