.query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

.query

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-09-22 : 11:37:47

Hi,
How can the following sql be written in .query ? something like

select
x.query
...
...

Thanks

select
Field1,
Field5,
Field6 =
case
when lower(Field6) = 'value1' then 1
when lower(Field6) = 'value2' then -1
end
FROM OPENXML (@idoc, '/Details/myDetail',2)
WITH (
Field1 int,
Field5 datetime,
Field6 varchar(10)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-22 : 11:47:32

[code]
SELECT Field1,Field5,CASE LOWER(Field6) WHEN 'value1' THEN 1 WHEN 'value2' THEN -1 END AS Field6
FROM
(
SELECT t.u.value('./Field1[1]','int') AS Field1,
t.u.value('./Field5[1]','datetime') AS Field5,
t.u.value('./Field6[1]','varchar(10)') AS Field6
FROM @idoc.nodes('/Details/myDetail')t(u)
)m
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-09-22 : 12:33:17

Thanks. Is this what I should always be using instead of openxml ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-22 : 13:09:28

yep. did you try it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-09-23 : 04:15:57

quote:
Originally posted by visakh16

SELECT Field1,Field5,CASE LOWER(Field6) WHEN 'value1' THEN 1 WHEN 'value2' THEN -1 END AS Field6
FROM
(
SELECT t.u.value('./Field1[1]','int') AS Field1,
t.u.value('./Field5[1]','datetime') AS Field5,
t.u.value('./Field6[1]','varchar(10)') AS Field6
FROM @idoc.nodes('/Details/myDetail')t(u)
)m

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I get this error:
The XMLDT method 'nodes' can only be invoked on columns of type xml.

Do I need to do anything before the sql you sent me?
I.e. declaring anything?
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-23 : 05:24:55

oh. wasn't variable @idoc of xml type? If no, you need to first make it xml

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-09-23 : 05:50:14

this is what I have but nothing is returned...
create procedure usp_test
@myXML xml = null
as
declare @idoc xml

and then you sql here...

No errors but nothing is returned.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-23 : 05:56:59

quote:
Originally posted by arkiboys

this is what I have but nothing is returned...
create procedure usp_test
@myXML xml = null
as
declare @idoc xml

and then you sql here...

No errors but nothing is returned.

where are you assigning value to @idoc?
you should assign xml document to @idoc or use variable that contains the xml elements in the query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-09-23 : 06:14:44

got it.
set @idoc = @myXML

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-23 : 06:37:33

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-09-23 : 12:11:37

Hello again,
It seems that the sql query you gave me, always uses the date - 1
so for example, the xml has field5 has '2011-07-29' but t.u.value('./Field5[1]','datetime') AS Field5 retrieves the date value as '2011-07-28 23:00:00'
Do you know why please?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-23 : 12:39:22

nope. thats not true. can you post a typical xml part with a date value for us to test?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-09-23 : 16:37:06

note that in below xml, there is +01:00
This is because from the front end, the xml is produced like this.

Is there a way to format this xml so that it does not show the +01:00 or somehow to ignore it? otherwise the sql query you sent takes this xml date and takes one day off it.

Thanks

declare @p1 xml
set @p1=convert(xml,N'<Customers><CustomerAdded>
<myDate1>2011-07-29T00:00:00+01:00</myDate1><myDate2>2005-04-01T00:00:00+01:00</myDate2>
</CustomerAdded></Customers>')
exec uspxml @CustomersXML=@p1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-24 : 04:52:11

yep. you can use modify method. see

http://msdn.microsoft.com/en-us/library/ms187093.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources