create procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

create procedure

Author

Topic

nitin05.sharma
Starting Member

20 Posts

Posted - 2012-04-25 : 08:41:45

i have one string of XML type
<NewDataSet>
<FT>
<Table>Table1</Table>
<FieldName>TableName</FieldName>
<Fieldvalue>T_NCO_LNK_AST_AC_MISSN_VARNT</Fieldvalue>
</FT>
<FT>
<Table>Table1</Table>
<FieldName>FieldName</FieldName>
<Fieldvalue>EFF_DT</Fieldvalue>
</FT>
<FT>
<Table>Table1</Table>
<FieldName>FieldValue</FieldName>
<Fieldvalue>123</Fieldvalue>
</FT>
</NewDataSet>

T_NCO_LNK_AST_AC_MISSN_VARNT is a table name and EFF_DT is the column of this table and 123 value of this column

i want to create a procedure to read this xml string ang update the value of this field EFF_DT in T_NCO_LNK_AST_AC_MISSN_VARNT
all these fields and corresponding column name come from this xml

please solve my problem

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-25 : 09:31:48

Since your table name and column names are not known in advance, you will need to use dynamic SQL to do this. When you use dynamic SQL, it opens up the possibility that malicious (or even non-malicious) data can wreak havoc on your database via SQL injection. So I would be very careful doing this, and avoid it if possible.

If you must do it, you can query the XML to find the name of the table, column name, and value and generate the update statements like this:

DECLARE @x XML; 
SET @x = '
<NewDataSet>
  <FT>
    <Table>Table1</Table>
    <FieldName>TableName</FieldName>
    <Fieldvalue>T_NCO_LNK_AST_AC_MISSN_VARNT</Fieldvalue>
  </FT>
  <FT>
    <Table>Table1</Table>
    <FieldName>FieldName</FieldName>
    <Fieldvalue>EFF_DT</Fieldvalue>
  </FT>
  <FT>
    <Table>Table1</Table>
    <FieldName>FieldValue</FieldName>
    <Fieldvalue>123</Fieldvalue>
  </FT>
</NewDataSet>';

;WITH cte AS
(
	SELECT
		c.query('data(FT[FieldName="TableName"]/Fieldvalue[1])')  AS TableName,
		c.query('data(FT[FieldName="FieldName"]/Fieldvalue[1])') AS FieldName,
		c.query('data(FT[FieldName="FieldValue"]/Fieldvalue[1])') AS FieldValue
	FROM
		@x.nodes('//NewDataSet') T(c)
)
SELECT
	'update ' 
		+ QUOTENAME(CAST(TableName AS VARCHAR(255))) + 
	' SET ' 
		+ QUOTENAME(CAST(FieldName AS VARCHAR(255))) + 
	' = ''' 
		+ CAST(FieldValue AS VARCHAR(255)) + ''''
FROM
	cte;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources