January 7, 2010 at 10:14 am
Hi there,
I got a stored procedure that receives an XML document as a parameter to update a table. The thing is the XML only has the fields that need to be updated.
Here I go... is there a way to parse the XML in the stored proc to update only the fields in it, or check if the node for exists in the XML?
I tried things like :
if exists (
SELECT * FROM tempdb.sys.tables WHERE [name] like '#testXML20100112%'
)
DROP TABLE tempdb.#testXML20100112;
GO
DECLARE@in_values varchar(90)
DECLARE @hDoc int
DECLARE @Id int
DECLARE @UpdateValue varchar(255)
SET @in_values = '<root><column_name><node_a>value1</node_a><node_b>value2</node_b></column_name></root>'
exec sp_xml_preparedocument @hDoc OUTPUT, @in_values
SET NOCOUNT ON;
SELECT * INTO #testXML20100112 FROM OPENXML (@hdoc, '/root/column_name', 3)
if exists(SELECT id FROM #testXML20100112 WHERE (localname = 'node_a'))
BEGIN
SET @Id = (SELECT id FROM #testXML20100112 WHERE (localname = 'node_a'))
SET @Id = @Id + 2
SET @UpdateValue = (SELECT text FROM #testXML20100112 WHERE (id = @id))
Print '"' + @UpdateValue + '"'
END
ELSE
BEGIN
Print 'No'
END
EXEC sp_xml_removedocument @hDoc
but nothing simple when you have 20+ fields... is there a better (read:faster) way? Or any other hints that could help me?
Thank you
January 7, 2010 at 10:28 am
What xml element/attribute describes the column name?
Your examle is only using data from a column. There is nothing indicating any relationship between node_a and localname, meaning: How do you know to use the localname column?
January 7, 2010 at 10:32 am
Or a way to populate a temp table with the XML and build a dynamic SQL update statement from the temp table...
January 7, 2010 at 10:41 am
lmu92 (1/7/2010)
What xml element/attribute describes the column name?Your examle is only using data from a column. There is nothing indicating any relationship between node_a and localname, meaning: How do you know to use the localname column?
That's one issue, that SELECT ... INTO isn't the best scenario...
Thing is my table has three fields : node_A, node_B and node_C and the XML I get has parameter for my stored proc only has the columns that need to be updated, if my XML string only has node_A and node_B because it,s the only fields that need to be updated, if I do this :
SELECT node_c FROM OPENXML (@hdoc, '/root/column_name', 3) WITH (node_c varCHAR(50))
I get 'NULL', but I can't know if the field node_c needs to be updated to null or if the node_c doesn't exist in my xml string...
Is there a way I can parse the XML and put the column names (node_x) and value in a temp table and create dynamically my Update statement.
January 7, 2010 at 10:49 am
TcW_1978 (1/7/2010)
but I can't know if the field node_c needs to be updated to null or if the node_c doesn't exist in my xml string...Is there a way I can parse the XML and put the column names (node_x) and value in a temp table and create dynamically my Update statement.
To your first question: This issue cannot be resolved using SQL. It's a problem of the concept. You need to get a "signal" if you have to set the column to NULL or you'd have to get all columns all the time with values in it.
Your second question: Yes it's possible. But how do you know which row to update? There should be an additional node indicating a rowId or something like that. Otherwise you're going to update ALL rows. Is this the intention?
January 7, 2010 at 11:04 am
1 : You're confirming what I thought, I dropped that idea, will never work.
2 : Well I went with the idea that if I can do something like this it will work...
Create a table tempupdatetbale with 2 columns : columnname and updatevalue
For each node_x
INSERT INTO tempupdatetbale VALUES (node_x, value)
DECLARE sqlStatement
sqlStatement = "UPDATE MyTable SET "
sqlStatement = sqlStatement + tempupdatetbale.columnname + '=' + tempupdatetbale.updatevalue
My problem is on the "For each node_x INSERT INTO tempupdatetbale VALUES (node_x, value)"... can't figure out how, put it seems to me like it is possible or at least I'm on the right path...
Meanwhile I found this thread: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=69684
Thanks
January 7, 2010 at 12:19 pm
To get the columns and the corresponding values you could use the following code (using XQuery and your sample data):
DECLARE @in_values xml
SET @in_values = '<root><column_name><node_a>value1</node_a><node_b>value2</node_b></column_name></root>'
SELECT DISTINCT
T.n.value('localname[1]', 'varchar(100)') AS ColumnName,
T.n.value('value[1]', 'VARCHAR(100)') AS val
FROM
( SELECT
x.query('
for $node in /descendant::node()[local-name(..) = "column_name"]
return <node>
<localname>{ local-name($node) }</localname>
<value>{ $node }</value>
</node>') AS nodes
FROM @in_values.nodes('/root') x(x)
) q1
CROSS APPLY q1.nodes.nodes('/node') AS T ( n )
/* result set:
ColumnNameval
node_avalue1
node_bvalue2
*/
January 7, 2010 at 10:46 pm
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply