|
Hi i want to update the "name" element in XML using the OPENXML in SQLServer. below is what i'm trying to do without success. Im not even sure if its possible. The value "xxxxx" is normally the name of a table, but when dealing with xml i'm not sure what should be there. Im passing in the xml as a parameter to the stored procedure and doing suff to it. but the last thing in the stored procedure is to update the "name" element. not sure how to do this need some help please. Thanks ONeil
update xxxxxxx
set OPENXML (@hDoc, '/Employees/Employee')
WITH (tName varchar(20) './Name') = 'UpdatedName'
|
|
|
|
|
Have a look at XML DML and especially updating a value: replace value of (XML DML)[^]
Hope this helps,
Mika
The need to optimize rises from a bad design
|
|
|
|
|
Thanks that helped alot. I manage to get cross that hurdle but now im faced with another problem. im getting error --- "The argument 1 of the xml data type method "modify" must be a string literal" --- for the below statement. I want to take the parameter @code and make it part of the insert statement. if i hardcode a value in the insert statement it work but once i reference the "@code" im getting "The argument 1 of the xml data type method "modify" must be a string literal". Any help would be appreciated
########## XML passing IN ###############
<Employees>
<Employee ID= '111'><Code>1</Code><Name>Name1</Name></Employee>
</Employees>
############# Stored Procedure ###########
ALTER PROC [dbo].[sp_Testing]
@empdata xml,
@code varchar(255)
AS
DECLARE @hDoc int
exec sp_xml_preparedocument @hDoc OUTPUT,@empdata
Select @empdata
set @empdata.modify('insert <Employee ID= ''101''>
'+@code+'</Code><Name>''Name101''</Name></Employee>into (/Employees)[1]')<br />
<br />
--return XML<br />
Select @empdata
|
|
|
|
|
Would this piece code perform the operation correctly? If I understood correctly, you want to insert an element to the xml data so you don't need to dynamically parse or concatenate the string.
declare @empdata xml
set @empdata = '<Employees><Employee ID= ''111''><Code>1</Code><Name>Name1</Name></Employee></Employees>'
Select @empdata
set @empdata.modify('insert <Employee ID= ''101''></Employee> into (/Employees)[1]')
Select @empdata
The result:
<Employees>
<Employee ID="111">
<Code>1</Code>
<Name>Name1</Name>
</Employee>
<Employee ID="101" />
</Employees>
Mika
The need to optimize rises from a bad design
|
|
|
|
|
It works fine if i hardcode the values in (as i said in my previous post and as you have done in your previous post). the problem is when i try passing in the "@code varchar(255)" as a parameter and use it to build the insert statement. that when im getting the error
|
|
|
|
|
Sorry about the misunderstanding, that's what you said in the first place (I'll have to go back to the english lessons and learn how to read  ).
Let's try something else. Note that the variable for the element to add is not varchar but xml (this is required).
declare @empdata xml
declare @somethingToAdd xml
set @empdata = '<Employees><Employee ID= ''111''><Code>1</Code><Name>Name1</Name></Employee></Employees>'
set @somethingToAdd = '<Employee ID= ''101''></Employee>'
Select @empdata
set @empdata.modify('insert sql:variable("@somethingToAdd") into (/Employees)[1]')
Select @empdata
Would this help?
The need to optimize rises from a bad design
|
|
|
|
|
Im feeling very stupid today. for some reason im always getting errors. copy, paste and try the below code. It parses ok but when Execute im getting this error ------- "The data types varchar and xml are incompatible in the add operator."
########################################
ALTER PROC [dbo].[sp_Testing2]
@empdata xml,
@code varchar
AS
DECLARE @hDoc int
declare @somethingToAdd xml
exec sp_xml_preparedocument @hDoc OUTPUT,@empdata
set @somethingToAdd = '<Employee ID= ''101''><Code>'+@code+'</Code><Name>''Name101''</Name></Employee>'
Select @empdata
set @empdata.modify('insert sql:variable('+ @somethingToAdd+') into (/Employees)[1]')
-- return the XMl
Select @empdata
####################################################
|
|
|
|
|
One problem is that you modified the insert literal (don't concatenate!).
If you don't need hDoc in somewhere else, the procedure would be in minimum something like this (note that @code is xml):
ALTER PROC [dbo].[sp_Testing2]
@empdata xml,
@code xml
AS
Select @empdata
set @empdata.modify('insert sql:variable("@code") into (/Employees)[1]')
-- return the XMl
Select @empdata
The need to optimize rises from a bad design
|
|
|
|
|
Thanks you have helped alot
|
|
|
|
|
You're welcome 
The need to optimize rises from a bad design
|
|
|
|
|
hi,
i am not very much sure that sql server database can be installed on windows xp os or not. whenever i am trying to install sql server database on windows xp os then information is showing that only client can be installed but database portion is disabled........so if anyone know that how to install sql server database on windows XP OS then plzz let me know with in detail info.
Thanks
tbhattacharjee
|
|
|
|
|
Yes it can be installed on XP, but SP level must be 2.
Depending on the version, you must first install client components and after that server portion. Also you must have sufficient privileges.
The need to optimize rises from a bad design
|
|
|
|
|
Please help me,
I want to connect to an mdf database (built by SQL Server 2005 Express) in VC++ using OLE DB. I can't do it. please help. please provide a code for me to connect to "c:\Database#1.mdf" using code.
Please help. Urgent.
|
|
|
|
|
|
yeah, no one cares if it urgent for you. Connection strings are one of the basic parts of programming. At least TRY to do it and if you get an error come back and we can look at your code. We're not going to do your work for you.
Blog link to be reinstated at a later date.
|
|
|
|
|
This is my code:
<br />
_ConnectionPtr m_pConnection;<br />
<br />
CoInitialize(NULL);<br />
<br />
m_csDataSource = <br />
"Provider=SQLNCLI;Server=.\\SQLExpress; \<br />
AttachDbFilename=C:\\DATABASE#1.MDF; \<br />
Trusted_Connection=Yes;"<br />
<br />
<br />
<br />
HRESULT hRes = m_pConnection.CreateInstance(__uuidof(Connection));<br />
if (SUCCEEDED(hRes))<br />
{<br />
hRes = m_pConnection->Open(_bstr_t((LPCTSTR)m_csDataSource),<br />
_bstr_t(L""),_bstr_t(L""),adModeUnknown);<br />
if (SUCCEEDED(hRes))<br />
{<br />
m_bIsConnectionOpen = TRUE;<br />
}<br />
}<br />
I get this error:
An attempt to attach an auto-named database for file C:\\Database#1.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located in on UNC share.
|
|
|
|
|
Why do I bother.
Blog link to be reinstated at a later date.
|
|
|
|
|
My Paranoid Hubby wrote: Why do I bother.
For the entertainment of others 
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
|
|
|
|
|
Hi,
I can add two different columns to an index. What is the difference if I make two separate indexes for two columns instead of creating one index and adding two columns to it.
Thanks
_
|
|
|
|
|
This depends very much on the database you are using (SQL Server, Oracle, MySQL etc).
But basically if you create two separate indexes, they can be used independently. When you have one index, the tree traversal must use the first column even though you are not referring to it in a query. Think of it like an index in a book. If you have two levels on chapters like:
- 1.1
- 1.2
- 1.3...
- 2.1
- 2.2
- 2.3...
and you want to find all chapters that are numbered ???.3 you must scan through the whole index to find what you are looking for.
Disclaimer: The description is really simplified and is not accurate for all databases so the situation is actually much more complex. But this should give you the idea.
Hope this helps,
Mika
The need to optimize rises from a bad design
|
|
|
|
|
Thanks for your response. To be more specific, I'm talking about SQL Server database. Suppose that I've got a query like this:
select id, fname, lname, age from customers where lname = @lname order by age asc
Besides having a default clustered index on id column, I can have an index containing two columns, lname and age. But I can also have an index for lname and an index for age column separately. Does it matter which one is better in this case?
I may also have another query like this:
select id, lname from customers order by age asc
for the sake of this query, according to what I learned from your explanation, having an index containing only age column would be better than an index containing lname and age. right?
Can I have both of aforementioned indexes together? Will SQL Server determine which index is better to use based on my query?
Thanks for your help
_
|
|
|
|
|
Having both indexes (lname, age and age) is ok. However this will have some performance penalty for insert, update and delete statements.
You could try having only lname + age or lname and age separately. The optimizer will pick up the index / indexes it will consider most benefitial. You can verify optimizer behaviour using execution plan.
If you have only lname + age, it is possible that the optimizer makes horizontal scan on the index tree for the second query.
If you have lname and age separately, the optimizer can choose to make an index join for the first query.
So try all variations and use execution plan to see what is reasonable in your case (especially consider that those are hardly the only queries, so you should consider all query needs for this table if possible).
It's like playing chess
Mika
The need to optimize rises from a bad design
|
|
|
|
|
Yea it's like playing a chess!
From what I've seen in execution plan, it tells me that how much percent of time has been spent on which phase of query. Do you have any recommendation for me on how to make best use of execution plan to fine tune my indexes? I'm sorry if this question is very general and might bother you.
Thanks again
_
|
|
|
|
|
I found an article here: Execution Plans[^]
I think it can help me a lot.
Thanks again for your help
_
|
|
|
|
|
Just noticed your message.
That article is a good one. Especially concentrate on the total cost (= estimated seconds) and how it's distributed. Then observe logical I/O amounts (don't mind so much about physical I/O).
Your question is not bothering me at all. Actually this area is one of my special interests
Happy coding! 
Mika
The need to optimize rises from a bad design
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
