|
Could you help figure out why my code not working?
DECLARE @xmlPolicyType XML
SET @xmlPolicyType= REPLACE('<?xml version="1.0" encoding="utf-16"?>
<NewDataSet>
<PolicyType>
MED
</PolicyType>
<PolicyType>
SPORT
</PolicyType>
</NewDataSet>
','utf-16','UTF-8')
DECLARE @xmlHandle INT
IF NOT @xmlPolicyType IS NULL
BEGIN
DECLARE @PolicyTypes TABLE
(
PolicyType CHAR(5)
)
EXEC sp_xml_preparedocument @xmlHandle output, @xmlPolicyType
--INSERT INTO @PolicyTypes
SELECT PolicyType
FROM OPENXML (@xmlHandle, '/NewDataSet/PolicyType',1)
WITH ( PolicyType CHAR(5) '@PolicyType' )
EXEC sp_xml_removedocument @xmlHandle
SELECT t.value('(PolicyType/text())[2]','nvarchar(120)')AS PolicyType
FROM @xmlPolicyType.nodes('/NewDataSet/PolicyType')AS TempTable(t)
|
|
|
|
|
What about it isn't working? Is there an error message? What database system?
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
|
Your OPENXML query doesn't work because the node value contains white-space, which is not getting trimmed. Since you're only taking the first 5 characters, you'll get a string containing nothing by white-space.
Your .nodes() query doesn't work because you're getting the PolicyType nodes, and then trying to find the third PolicyType node within that node.
Try this:
DECLARE @xmlPolicyType XML
SET @xmlPolicyType= REPLACE('<?xml version="1.0" encoding="utf-16"?>
<NewDataSet>
<PolicyType>
MED
</PolicyType>
<PolicyType>
SPORT
</PolicyType>
</NewDataSet>
','utf-16','UTF-8');
DECLARE @PolicyTypes TABLE
(
PolicyType char(5)
);
INSERT INTO @PolicyTypes
(
PolicyType
)
SELECT
LTrim(Replace(Replace(Replace(
T.PolicyType.value('.', 'varchar(120)')
, Char(13), ''), Char(10), ''), Char(9), ''))
FROM
@xmlPolicyType.nodes('/NewDataSet/PolicyType') As T (PolicyType)
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I create a new SQL CE database and try to add two tables with a key linkiing them. The first table is created successfully with the statement:
CREATE TABLE Classes(ClassId INTEGER NOT NULL PRIMARY KEY, Class NVARCHAR(64) NOT NULL )
and I add 3 rows to it successfully.
I then try to create the second table with a foreign key thus:
CREATE TABLE Accounts(Account NVARCHAR(64) NOT NULL, Telephone NVARCHAR(128), ClassId INTEGER FOREIGN KEY REFERENCES Classes(ClassId) )
but this fails with the message:
The constraint specified is not valid
What am I doing wrong?
|
|
|
|
|
Have a read of the first answer in this StackOverflow Question[^] on the same subject.
The first answer suggests that you remove the FOREIGN KEY part of your create table statement
Every day, thousands of innocent plants are killed by vegetarians.
Help end the violence EAT BACON
|
|
|
|
|
Thanks for the reply, my syntax was wrong, Eddy found the answer.
|
|
|
|
|
Richard MacCutchan wrote: What am I doing wrong?
Specifying the constraint, below code should work for sqlite3;
The question is about SQL CE 
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Thanks Eddy, your suggestion did work (for SQL CE). I have been staring at this for over an hour and could not see the subtlety of the optional parameters in the documentation[^].
[edit]
For the record the correct syntax is:
CREATE TABLE Accounts(Account NVARCHAR(64) NOT NULL, Telephone NVARCHAR(128), ClassId INTEGER, FOREIGN KEY (ClassId) REFERENCES Classes(ClassId) )
[edit]
modified 31-Jul-14 9:18am.
|
|
|
|
|
Richard MacCutchan wrote: your suggestion did work (for SQL CE).
Cool - one of those rare cases where both implemented the same sql-standard.
Makes me curious how easy (or hard) it is to switch from one to the other.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Dear All,
I would like to show you the following questions ( SQL server ):
- I have one procedure that return 2 result after execute
Eg:
create procedure spTest
as
begin
select * from table1
select * from table2
end
( this is just sample procedure that return 2 result after execute )
- I also have one temp Table name #tmpTable1, i want to insert into this temp table, but get result from procedure above, but i can't use this statement:
insert into #tmpTable1 exec spTest
( it will not work, because this procedure return 2 result )
Do you have any idea to get result from this procedure into my temp table?
Thank you in advance for your good idea...
Mr. LTM KH
|
|
|
|
|
You have a some of choices, the obvious one is to split the child procedure into 2 procs, this is by far the better choice.
You could also use a global temp table in the child procs declare as ##TableName. IE create the global temp in the parent proc and call the insert proc, then consume the results in the parent proc. I would consider this a really crappy solution as it is fraught with problems.
You could also modify the child proc to use a UNION and return only 1 table (assumes if you are going to insert the data into 1 table then it is the same structure)
MS did a great disservice when they allowed multiple result sets to be returned from a single proc.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Dear Mycroft Holmes,
Thanks you so much for your solutions, I will test it with global temp table...
But this may not the best choice for me..
Regard
Mr.LTM-KH
|
|
|
|
|
LTM_it_kh wrote: But this may not the best choice for me.
It may well be the worst solution! Global temp table are nasty to manage!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I want to calculate Total Time difference like this
eg: 32:10 - 26:10 = 06:00
How to do this with the sql query?
|
|
|
|
|
What database are you using?
Is 32:10 minutes:seconds?
What have you tried?
|
|
|
|
|
32:10 means
32 Hours and 10 Minutes, I want to find this Total Hours Difference in Hour and Minutes
|
|
|
|
|
What you might want to do is convert the hours to minutes then subtract then convert back.
|
|
|
|
|
In MS-SQL you can try
SELECT DATEDIFF(hour, START_DATE,END_DATE) FROM YOURTABLE
|
|
|
|
|
Hi,
----------
Note: I'm making an assumption that time values are given inputs as VARCHARs (data type cannot be changed for some reason). Also, please take a look at Mycroft's comments and answer below.
----------
You can create a user-defined function for this purpose. Here is an example of such a function for SQL Server:
CREATE FUNCTION [dbo].[ufn_GetTimeDifference] (@SecondTime VARCHAR(20),
@FirstTime VARCHAR(20))
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @TotalMinutes INT,
@IsNegative BIT = 0,
@Hours INT,
@Minutes INT,
@TimeDifference VARCHAR(20);
SET @TotalMinutes = ( ( LEFT(@SecondTime, ( CHARINDEX(':', @SecondTime) - 1 )) * 60 ) + RIGHT(@SecondTime, 2) ) -
( ( LEFT(@FirstTime, ( CHARINDEX(':', @FirstTime) - 1 )) * 60 ) + RIGHT(@FirstTime, 2) );
IF ( @TotalMinutes < 0 )
BEGIN
SET @IsNegative = 1;
SET @TotalMinutes = ABS(@TotalMinutes);
END
SET @Hours = ( @TotalMinutes / 60 );
SET @Minutes = ( @TotalMinutes % 60 );
SET @TimeDifference = ( CASE
WHEN ( @IsNegative = 1 ) THEN '-'
ELSE ''
END ) +
( CASE
WHEN @Hours < 10 THEN '0' + CAST(@Hours AS VARCHAR(1))
ELSE CAST(@Hours AS VARCHAR(20))
END ) +
':' +
( CASE
WHEN @Minutes < 10 THEN '0' + CAST(@Minutes AS VARCHAR(1))
ELSE CAST(@Minutes AS VARCHAR(2))
END );
RETURN @TimeDifference;
END
GO
As you can see, second time and first time parameters are varchars, which expects time in the HH:MM format. The function returns positive or negative time difference as varchar.
Examples of usage:
SELECT [dbo].[ufn_GetTimeDifference]('32:10', '26:10') AS 'Time Difference';
06:00
SELECT [dbo].[ufn_GetTimeDifference]('72:00', '36:33') AS 'Time Difference';
35:27
SELECT [dbo].[ufn_GetTimeDifference]('25:25', '25:25') AS 'Time Difference';
00:00
SELECT [dbo].[ufn_GetTimeDifference]('26:10', '32:10') AS 'Time Difference';
-06:00
Regards,
Andrius Leonavicius
modified 30-Jul-14 17:45pm.
|
|
|
|
|
This is a bad answer, recommending that someone uses varchar for date function is the WORST thing you can do, globalisation (and crappy data) screws them every time. You should only accept a datetime format and use the databases datetime functionality to deal with it.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
Thanks for the reply.
First of all, I'm not recommending that someone should use VARCHAR for storing time (date) values. I agree on this part with you.
However, I'm looking at this problem from a different perspective than you do. I'm considering the usage of VARCHARs as given inputs (data type cannot be changed for some reason). Let's take a look at OP's example once more: "eg: 32:10 - 26:10 = 06:00". So 32:10 and 26:10 are VARCHARs and they cannot be converted to DATETIME...
I made a really bad assumption that OP is aware of DATETIME usage in such situations (as you said) and haven't warned about that.
Regards,
Andrius Leonavicius
modified 29-Jul-14 20:25pm.
|
|
|
|
|
Andrius Leonavicius wrote: I'm considering the usage of VARCHARs as given inputs (which cannot be changed for some reason)
That is a premise I would not accept, if it gets to the database then it must be in a valid format, the OP should be going back to the input and cleaning up the data before it gets into the database. He has more fundamental problems than getting the difference here.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Well, you're right. +5 for your answer.
Regards,
Andrius Leonavicius
|
|
|
|
|
Robymon wrote: 32:10 - 26:10
As has been pointed out by others this is NOT a valid data format, either change the format to 2 ints (hours and minutes) or convert them to minutes. You can then work on the data using math or datetime functions.
While Andrius solution is valid for chopping up strings you should not be dealing with strings in the first place. Fix your underlying data problems and this issue disappears.
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
