|
Good Morning All
i have the Following sql statement
DECLARE @idoc int
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @TagXml
SELECT *
into [#Tag]
FROM OPENXML (@idoc, '/Tag',2) -- be very careful this is case sensitive
WITH ( ID int, Descr varchar(128), TypeID int, Note varchar(256), Static varchar(7), objID int )
-- get the list of ids from the edge table
SELECT convert(int, convert(varchar(10), [Text]) ) AS [ID]
into [#ObjList]
FROM OPENXML (@idoc, '/Tag/objID',2) -- be very careful this is case sensitive
WHERE [Text] is not null
Now from the above statements i understand this
DECLARE @idoc int
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @TagXml
and the Problem comes here
-- get the list of ids from the edge table
SELECT convert(int, convert(varchar(10), [Text]) ) AS [ID]
into [#ObjList]
FROM OPENXML (@idoc, '/Tag/objID',2) -- be very careful this is case sensitive
WHERE [Text] is not null</pre>
I see that they convert varchar to int and call that ID , its cool i understand that and save it in a temp table. Now the unknown part is the part where
FROM OPENXML (@idoc, '/Tag/objID',2) -- be very careful this is case sensitive
WHERE [Text] is not null</pre></pre>
I understand what their doing, my Problem is that "2" why are they doing there. here is the Input example of the xml
'<Tag> <ID>0</ID> <Descr>new_taggy</Descr> <TypeID>1</TypeID> <Note /> <Static>true</Static></Tag>'
And MSDN Says
2 Use the element-centric mapping.
Can be combined with XML_ATTRIBUTES; in which case, attribute-centric mapping is applied first, and then element-centric mapping is applied for all columns not yet dealt with.
Can you please explain for me.
Thank you
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
www.ITS.co.za
modified on Thursday, March 26, 2009 5:32 AM
|
|
|
|
|
Hi,
I want to load xml file i to the sql server 2005 table.
Foe e.x i have a xml file containing details
'<'employee'>'
'<'firstname'>'abc'<'/firstname'>'
'<'lastname'>'xyz'<'/lastname'>'
'<'/employee'>'
now i want to load this data into the table in sql server 2005 database.
The table contains 2 columns: firstname and lastname
i want to load this xml data to this table in to the corresponding column.
so is it possible through T-sql query.ID yes,please let me know and if not please give me some alternative to load this xml data in to the table.
Regards,
pranav.
Pranav Dave
|
|
|
|
|
|
hi
see this
http://www.codeproject.com/script/Forums/View.aspx?fid=1725&msg=2932578[^]
Hope it Helps
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
www.ITS.co.za
|
|
|
|
|
hello
QUESTION 1: I scouted around a little bit, seems like in MySQL there's no such thing as NTEXT, only TEXT? Am I right?
http://dev.mysql.com/tech-resources/articles/visual-basic-datatypes.html
QUETSION 2: In MySQL you have DATETIME and TIMESTAMP, with TIMESTAMP you can have a table column to default to "current_timestamp" (but for DATETIME, in MySQL, it can only DEFAULT to NULL)
I hesistated to declare column as TIMESTAMP because my nhibernate file uses NHibernate type DateTime:
<component name="BasicAudit" class="Util.Security.BasicAudit">
<property name="CreateDate" column="CreateDate" type="DateTime" />
...
</component>
QUESTION 3: I am using VARBINARY in MySQL
create table xxx
{
...
blob varbinary(MAX), -- you don't have MAX as in M$SQL, what should I use in MySQL?
...
);
QUESTION 4. Reading blob - haven't got around to test this feature yet, big util lib ... is this right?
...
if (!Convert.IsDBNull(oRdr["blobValue"]))
{
byte[] blobValue = new Byte[(oRdr.GetBytes(10, 0, null, 0, int.MaxValue))];
oRdr.GetBytes(10, 0, blobValue, 0, blobValue.Length);
oValue.BlobValue = blobValue;
}
...
QUESTION 5. Does this corresponds to System.Data.DbType.Binary? I need to make this work for M$SQL/MySQL/Oracle. I understand there's "SqlDbType.VarBinary" but that's M$SQL specific and this is not available for IDbParameter (Db vendor neutral version of db param class in .net)
QUESTION 6: Is it true that you can only declare variable within stored proc or UDF in MySQL?
After scouting around for a long time it seems to be you can only declare variables from within storedproc or function!!
http://dev.mysql.com/doc/refman/5.0/en/if-statement.html
QUESTION 7. Is there an equivalent of TSQL PRINT statement (or dbms_output.put_line in case of oracle) in MySQL?
Thanks
dev
<div class="ForumMod">modified on Wednesday, March 25, 2009 10:43 PM</div>
|
|
|
|
|
ANS 1: Thats correct.
ANS 2: You are right. One can not use CURRENT_TIMESTAMP() function as a defult value for DATE and DATETIME datatypes in MySQL. However, this function can be used in TIMESTAMP datatype.
Additionally, one can also use NOW() function as a defult value for TIMESTAMP datatype.
ANS 3: MAX can not be used for VARBINARY.
The allowable maximum length is the same for VARBINARY as it is VARCHAR, except that the length for VARBINARY is a length in bytes rather than in characters.
ANS 4,5: Thats the way!
ANS 6: Variables can be declared and accessed outside Procedure and Functions as follows:
---------------------------------------
mysql> SET @a = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> select @a;
+------+
| @a |
+------+
| 1 |
+------+
1 row in set (0.01 sec)
---------------------------------------
ANS 7: SELECT statements can used directly in Procedures/Functions in MySQL for viewing desired output as follows:
---------------------------------------
mysql> DELIMITER //
mysql> CREATE PROCEDURE print_test()
-> BEGIN
-> SELECT 'This is test printing' AS "Output";
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql> call print_test();
+-----------------------+
| Output |
+-----------------------+
| This is test printing |
+-----------------------+
1 row in set (0.00 sec)
---------------------------------------
|
|
|
|
|
|
I have to syncronize two dbs one db is running SQL 2000 SP4 Ent
edition
The other SQL 2005 SP2 Ent edition
Goal is to have data be availabe as read\write on both systems and get
as close to real time replcaition from the SQL 2000 box to the SQL
2005 box as possible. The 2000 box will be sunsetted soon
I know I can take a full back up restore and do log shipping with
recover every 5 minutes or so but that seems to not be a good option.
Anyone have any ideas?
Thanks,
Regards,
Hulicat
|
|
|
|
|
Hi
I need any comparison between SQL server and Oracle (...types...function...)
thank's in advance
|
|
|
|
|
|
Hi Eddy,
Most often when Compare(thing1, thing2) returns a negative value, it indicates thing1 is less (or slightly less) than thing2. Is that what you intended to say?
Luc Pattyn [Forum Guidelines] [My Articles]
- before you ask a question here, search CodeProject, then Google
- the quality and detail of your question reflects on the effectiveness of the help you are likely to get
- use the code block button (PRE tags) to preserve formatting when showing multi-line code snippets
|
|
|
|
|
Whehe I had to read it twice, but strCompare doesn't compare the things but the strings that we use to denote those things.
..and no, I did intend to say what you are saying, not what I said. Thanks for making me say what I meant to say
I are troll
|
|
|
|
|
I looking for differences in programming , example: TO_CHAR vs STR...SYSDATE vs GETDATE()....
|
|
|
|
|
E_Gold wrote: I looking for differences in programming , example: TO_CHAR vs STR...SYSDATE vs GETDATE()....
That would be a list with the differences between TSQL and PL/SQL, not between the differences of SQL Server and Oracle.
The list of features of both products (and both SQL-dialects) is freely available. Start with making a list, then you'd be checking it twice..
If you're looking for a place to start, this[^] one might be as good as any starting point.
I are troll
|
|
|
|
|
I have a textbox that has its text databound. As soon as I put it into a ToolStripDropDown via the ToolStripControlHost it becomes disconnected from the database. Does anyone know a way to preserve or reinstate the binding?
I have already tried this but nothing happens:
TextBox1.DataBindings.Clear()
TextBox1.DataBindings.Add("Text", DBBindingSource, "Description")
SSDiver2112
|
|
|
|
|
Hi Guys,
I have a Database in Access I want to upgrade it to MSSQL2005.
Could you please give me few tips to upgrade my database..
Or do I need to rewrite all tables again in MSSQL2005.
I never used DTS before.
Thanks
|
|
|
|
|
Depending on your installation, you might have an upsize-wizard [^]in Access that can move everything to SQL Server. It should do the trick within a matter of minutes.
I are troll
|
|
|
|
|
Hi
I have an interesting problem. I have written an SP to return some stats. When I run this through SQL Management studio - the result returns in 2 seconds or less. Now when I call this through asp.net - it takes anywhere from 2 minutes to 10 minutes+ (asp.net times out). I have tested this on my machine, and one of our offline production web servers. Now, when I attach the debugger to my asp.net code, where it makes the call, results are just as quick as when i run it through the management studio.
Yesterday I thought I was onto something - the user I was connecting with, was being used by multiple sites and applications. I created a new user and tried using this. Now asp.net was returning the records as quickly as expected. However, this morning the performance has gone back to what it was, and it takes ages to run.
I have connected to SQL management studio using the user credentials that the asp.net site is using (SQL connection string). When I run this, results return in 2 seconds. I also looked at the activity monitor - when I run this through management studio, CPU time shows as around 1000. When I run it through asp.net - this rises to between 100 000 and 400 000 in the CPU time.
I am not using cursors, this is just a select, and usually returns 10 - 50 records at most. The query does access multiple databases, some of which are on a linked server.
Thanks,
Wayde
|
|
|
|
|
|
I have written some triggers that fire when data is updated/inserted/deleted. These triggers write entries to the Audit tables.
I want to include the username of the person who made the change. I started by using SUSER_SNAME which works great on the surface.
The trouble is that our system is n tier. There is a business logic server that does all of the access to the database. users access the system via a client app which communicates with the BLL server which writes to the database. Users never communicate with the database directly. This means that the audit logs all have "serveruser" in them, which is the user the server runs under.
Is there any way I can get the audit logs to reflect the user that told the server to make the change, rather than the user that the server is running under.
We don't use stored procedures (we are using a combination of the entity framework and parametrized queries) so we can't just pass the user name to them and let them do it.
(Perhaps is there something to do with impersonation that would do this? I don't want to grant access to the database to any normal users though.)
(I'm using SQL server 2000 and .net 3.5)
Any ideas?
[Edit: I have a solution.
Include a LastModifiedBy field on the table. This value is then set by the server to match the user that made the call to the server. This field is then accessible from the trigger so can be set into the audit table.
What do people think of that solution? Does it sound sensible? Are there any pitfalls of this I should be aware of?]
Simon
modified on Tuesday, March 24, 2009 8:44 AM
|
|
|
|
|
The only effective way I've ever been able to do this is to pass the identity down through the tiers.
"WPF has many lovers. It's a veritable porn star!" - Josh Smith As Braveheart once said, "You can take our freedom but you'll never take our Hobnobs!" - Martin Hughes.
My blog | My articles | MoXAML PowerToys
|
|
|
|
|
Thanks for replying so quickly Pete.
Pete O'Hanlon wrote: The only effective way I've ever been able to do this is to pass the identity down through the tiers.
Yeah, we do currently do that. We have a session object that contains a bunch of user related stuff for each call to the server, and that is passed down through each tier (it's used to validate that the user has permission to do what they are requesting, and to filter returned results to only stuff that the user is allowed to see), but how do I get the the SQL server triggers to use that user name. We don't use stored procs so I can't just pass the user name into the stored proc.
Simon
|
|
|
|
|
I am working on a database project using oracle 10g database and asp.net. On the page, i have configured crystalreportviewer,crystalreportsource and a SQLDataSource for crystalreportsource. While configuring SQLDatasource it is asking me the input paramter but i do'nt know how can i pass sys_refcursor parameter from design/configuring time. Here is my code of stored procedure:
create or replace procedure get_emp(emn in integer,rc out sys_refcursor)
is
begin
open rc for select * from emp where empno=emn;
end;
/
This is working fine in SQLPlus as :
VARIABLE cv REFCURSOR
EXEC GET_EMP(7788,:CV);
print cv;
and it is working fine. I am not able to pass the "CV" from code/design time.
Please guide and help me.
Thanks and Regards
Girish Sharma
|
|
|
|
|
I have created a table with "bytea" datatype column. (I have not used oid datatype intentionally as its a pointer) I am using Windows XP machine. How can I store an image here?
Like in MySQL, actual image can be stored in a table with BLOB or Medium BLOB datatype.
|
|
|
|
|
Hi I Have Some Oracle question
1. How to activate Oracle Server and How to Close ?
2. How to connect to database ?
thank's
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
