sql server-关于动态sql构造

Question

当我们将xml加载到游标中时，我们指定列名及其数据类型和大小。而不是手动指定如何使该区域动态。假设我的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) 

等等，我正在指定并且还指定它们的数据tyoe&大小

那么我如何动态地构造这个区域并获取列名和数据类型&动态调整大小。

请引导我，谢谢。

Answer 1

您可以获得这样的列名（它们是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子句的列名和值。然后，您可以根据需要构建动态语句。

Answer 2

通常数据类型和字段名的信息在XSD文件中描述（XML模式定义）。

因此，您需要为每个XML文件都有一个有效的XSD文件，然后才能检索字段名称和数据类型。

这里有一个链接，可以更好地理解XSD

这里介绍如何逐步处理XSD和XML

希望它能帮助你