Updating from a XML

Question

0.00/5 (No votes)

See more:

Is the below thing possible?

I have a long XML string from which I select data. This data is the updated rows for a table, would it be possible to do something like this:

SQL

EXEC sp_xml_preparedocument @Handle OUTPUT, @Tags

UPDATE
    Pharmacy.Bins
SET
FROM
    (SELECT 
	    Notes 
    FROM 
		OPENXML(@Handle,'/data/Bins',2) 
		WITH (BinID int, ClosetID int, Notes nvarchar(MAX))
	) AS O
    JOIN Pharmacy.Bins AS B
        ON O.BinID = B.BinID
	)

Posted 14-Jun-10 3:46am

Mustafa Ismail Mustafa

Updated 14-Jun-10 4:37am

Sandeep Mewara

v2

Add a Solution

Comments

Sandeep Mewara 14-Jun-10 10:39am

It would be nice if you can add what exactly you are trying to achieve here? What do you have and what is the end result expected?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sandeep Mewara · Answer 1 · 2010-06-14T04:42:00

Solution 1

It is difficult to look here and tell if it's possible or not. Please have a look at the details of using XML in SQL here:
MSDN: XML Support in Microsoft SQL Server 2005[^]

You should get an idea of what things are possible.

Posted 14-Jun-10 4:42am

Sandeep Mewara

Comments

Mustafa Ismail Mustafa 15-Jun-10 5:19am

No you missed out on my intent Sandeep.

The way I thought of it was this:

1. Selects return a "tabular" result set
2. I can update one table from another
3. I should be able to perform the same but from a select statement, which happens to retrieve data from an XML string

So, I figured it would be something like this:

UPDATE [table name]
SET
FROM
(SELECT statement returning required data from XML)
JOIN
(Join)

Makes more sense now?