|
Table tblMyTable has a field myXMLField with datatype xml
Question:
I would like to pull out the values from each record and then group them so that only shows the distict values.
How is this done please?
Thanks
This is what I have at present but it is not correct
select
stringList.value('(/value)')
from
tblMyTable
The records inside the xml field in the table is similar to:
record1 shows:
<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0">
<value>value1</value>
</stringList>
record2 shows:
NULL
record3 shows:
<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0">
<value>value5</value>
<value>value2</value>
</stringList>
record4 shows :
<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0">
<value>value4</value>
<value>value1</value>
<value>value5</value>
<value>value2</value>
</stringList>
RESULT:
value1
value2
value4
value5
|
|
|
|
|
I think there's some problems with the namespace. If you give it a name then query method works fine. For example:
DECLARE @x xml
SET @x = '<stringList xmlns<big>:A</big>="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value1</value></stringList>'
SELECT @x.query('data(/stringList/value)'
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi,
That does not solve the issue.
I have modified the sql as follows. The error says Syntax error near 'stringList'
Any thoughts please?
DECLARE @Sample TABLE
(
rowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
[xml] XML
)
INSERT @Sample
SELECT '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value1</value></stringList>' UNION ALL
SELECT NULL UNION ALL
SELECT '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value5</value><value>value2</value></stringList>' UNION ALL
SELECT '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value4</value><value>value1</value><value>value5</value><value>value2</value></stringList>'
SELECT *
FROM @Sample
SELECT DISTINCT t.c.value('.', 'VARCHAR(20)')
FROM @Sample AS s
CROSS APPLY s.xml.nodes('/stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"/value') AS t(c)
|
|
|
|
|
That wasn't exactly my point.
Enclose field named xml in brackets (is that correct word for [ and ]?) like:
CROSS APPLY s.[xml].nodes...
Then if you run the query you have, you'll get empty results. But if you have an alias (like A) for the namespace, the query runs fine:
...xmlns:A="http:...
Also you don't have to use namespace in the xquery.
Here's the full code:
DECLARE @Sample TABLE
(
rowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
[xml] XML
)
INSERT @Sample
SELECT '<stringList xmlns:A="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value1</value></stringList>' UNION ALL
SELECT NULL UNION ALL
SELECT '<stringList xmlns:A="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value5</value><value>value2</value></stringList>' UNION ALL
SELECT '<stringList xmlns:A="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value4</value><value>value1</value><value>value5</value><value>value2</value></stringList>'
SELECT *
FROM @Sample
SELECT DISTINCT t.c.value('.', 'VARCHAR(20)')
FROM @Sample AS s
CROSS APPLY s.[xml].nodes('/stringList/value') AS t(c)
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
|
No problem.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
i created a job to transfer data in one table to another table, it's working perfectly, but the problem is when data is transfering because of primary key vales it gives an error(primary key duplication), how can i insert data without getting an error
one possible thing is dalete every thing in the second table and then insert every thing in the first table to the 2nd.but i don't think that this is a good solution , i'm expecting a better solution.
|
|
|
|
|
If you want to update table2 from the matchuing records in table 1 and then insert any new ones
Update table2
set col1 = a.col1,.....
from table2 b, table1 a
where b.primarykeycol = a.primarykeycol
insert into table2
select col1,col2.... from table1 a
where not exists (select 1 from table2 b where b.primarykeycol = a.primarykeycol)
If you don't want to update table2 ignore the update.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
thanx, but there is another problem, the two tables are in two servers ,assume-> table1 is in server A (sql 2000) and the table 2 in server B (sql 2005), so i want to do the transfer from server A to server B . i tried it with
insert into table_B (eid,ename,eaddr)
select r.eid,r.ename,r.eaddr
from [server A ip]\[sql instance]\[db]\table_A r
but get an error at the srver ip
|
|
|
|
|
You need to investigate LINKEDSERVERS. The alternative is to bcp the data out of table1 and into table2, but I guess you don't want to go that way.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
How did you write the job in the first place?. I havent had much luck writing code to transfer data from a server to server. But if you have that ability then I can help with avoiding primary key duplication
|
|
|
|
|
i tried on the net and in MS site for this script, but the script was removed , can anyone tell me where i can find the script
|
|
|
|
|
|
tanx, i so it but i didn't try because i was desinged to sql 2000, at the moment i'm in the middle of my testing project i don't know if this is the same one which came with sql 2005.
|
|
|
|
|
As far as I am aware (however I could be wrong), SQL Server 2005 did not come bundled with the Northwind Database, nor has the actual Database changed since SQL Server 2000. I tend to just run the script on my 2005 instance, and it works fine with all sample code.
If you have any troubles using it, feel free to ask.
|
|
|
|
|
|
I am not too good with T-SQL...
I have a Temp Table with the following data:
Date Line WGT .... Form
XXX 1 126 .... ABC
XXX 1 124 .... ABC
XXX 1 126 .... EFG
XXX 2 126 .... ABC
XXX 2 123 .... EFG
XXX 8 124 .... EFG
XXX 8 126 .... EFG
XXX 8 127 .... HIJ
XXX 8 126 .... HIJ
...
I need to query this table as follows:
Select the Rows for Each 'Line' that have the lowest 'WGT' where the FORM value is distinct for that 'LINE'.
Does That make sense?
Output should be like:
DATE LINE WGT .... FORM
XXX 1 124 .... ABC
XXX 1 126 .... EFG
XXX 2 126 .... ABC
XXX 2 123 .... EFG
XXX 8 124 .... EFG
XXX 8 126 .... HIJ
...
I have tried every variation of distinct, min and group by that I can think of. I am thinking of resorting to UDF's but I know deep down in my inner Geek that this is simple. Any tips before I bore apall you with my pathetic T-SQL?
CODEZ nOT URGNT  
I don't speak Idiot - please talk slowly and clearly
|
|
|
|
|
Not too hard, the basic principal is to create a query that gets the distinct line/form along with the min wgt and then join this back to the main table to get the final results.
My table was named "test" and I got the results you expect with this query:
select test.date,test.line,test.wgt,test.form
from test
inner join
(
select line,form,min(wgt) as minWgt
from test
group by line,form
) groupedTest
on test.line = groupedTest.line
and test.form = groupedtest.form
and test.wgt = groupedtest.minWgt
|
|
|
|
|
Excellent - just what I needed to point me in the right direction.
My actual query is much more complicated that the outline I gave, but I was able to expand your sample to complete the job.
Many thanks
I don't speak Idiot - please talk slowly and clearly
|
|
|
|
|
All,
I have created LinkedServer and try to run a query. it is throwing error like
"
OLE DB provider "SQLNCLI" for linked server "SRCSVR" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "SRCSVR" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
Msg 53, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [53].
"
My SP_AddLinedServer is
"
exec sp_addlinkedserver
@server='SRCSVR',
@srvproduct= '',
@provider='SQLOLEDB',
@datasrc='SERVER=MyServer;UID=user;PWD=pass'
"
I tried to execute this Query
select Name from SRCSVR.DBNAme.dbo.TableName
I have enabled
Firewall
tcp/ip
Named Pipes
But still it comes.
Please let me help to overcome this problem.
Thanks
Praveen Kuamr K
|
|
|
|
|
Is the server you're trying to connect to, default instance. If not try adding instance name after server name (if i remember correctly, @datasrc='SERVER=MyServer\InstanceName;UID=user;PWD=pass')
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi all,
I have a .sql script file and wanted to use the following code to write it into one of the database. How should I configure the connection string? I'm new to database programming.
using System.Data.SqlClient;
using System.IO;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
string sqlConnectionString = "Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=True";
FileInfo file = new FileInfo("C:\\myscript.sql");
string script = file.OpenText().ReadToEnd();
SqlConnection conn = new SqlConnection(sqlConnectionString);
Server server = new Server(new ServerConnection(conn));
server.ConnectionContext.ExecuteNonQuery(script);
}
}
}
|
|
|
|
|
|
I used the following string:
"Driver={SQL Native Client};Server=MACHINE_NAME\\SQLEXPRESS ;Database=C:\\tset.mdf;";
but ended up with exception saying "Driver" is an unsupported keyword and when I removed this keyword the unhandled exception occurred.
|
|
|
|
|
You should use the one SqlConnection (.NET) section
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
