CODEWITHSUNDEEP
sqlsql-servert-sql
Thomas
Thomas

Reputation: 34188

Regarding Dynamic sql construction

when we load xml into cursor then we specify column name and their datatype and size. instead of specifying manually how could i make that area dynamic. suppose my tsql as follows

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)
)

here as a example

productid Varchar(10),
unit Varchar(50)

etc i am specifying and also specify their data tyoe & size.

so how could i construct this area dynamically and fetch the column name and data type & size dynamically.

please guide me thanks.

Upvotes: 0

Views: 188

Answers (2)

Dalex
Dalex

Reputation: 3625

You can get column names(which are nodes inside PurchasePrderDetail node) like this:

 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)

So you can generate dynamic SQL statement to create cursor with appropriate column names and length like varchar(MaxLength).

But you can not get datatypes from XML without knowing real column names because data in xml is just text and f.e. "5" can be int type and also just a text.

EDIT

If you know table name, you can built dynamic SQL statement using metadata from that table using this:

; 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

As output you will have column name and value with appropriate CAST clause. Then you can build dynamic statement what you need.

Upvotes: 1

Luka Milani
Luka Milani

Reputation: 1541

Usually the information of data types and field names are describes in the XSD file (XML Schema Definition).

So you need to have a valid XSD file for each of your XML file then you can retrieve the fields name and data type.

Here a link to understand better the XSD

And here how to deal with XSD and XML step by step

Hope it helps you

Upvotes: 0

Related Questions