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 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy