Time stamp in XML gives Problem.

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 2005 Forums

Transact-SQL (2005)

Time stamp in XML gives Problem.

Author

Topic

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-07-21 : 08:47:15

I have a table like this .


create table t1(id int,[timestamp] timestamp ) 

insert into t1 (id) values (1)
insert into t1 (id) values (2)

select * from t1

id          timestamp
----------- ------------------
1           0x0000000000570AA4
2           0x0000000000570AA5

AND I have to process the XML like this.

DECLARE @l_xmlout	INT,
	@l_xmlin  XML	
set @l_xmlin = '<root>
	<subnode>
	<ID>1</ID>
	<Timestamp>0x0000000000570AA4</Timestamp>
	</subnode>

	<subnode>
	<ID>1</ID>
	<Timestamp>0x0000000000580AA4</Timestamp>
        </subnode>
	</root>' 

	EXEC SP_XML_PREPAREDOCUMENT @l_xmlout OUTPUT, @l_xmlin

select XL.ID,XL.Timestamp	 from openxml(@l_xmlout,'/root/subnode',2 )
with
    (
	ID	      int,
	[Timestamp]   timestamp  -- varchar(100)
	)as XL  join  t1 on t1.ID = XL.ID 
	--  and XL.Timestamp  = t1.Timestamp --where  t1.ID  is null

EXEC SP_XML_REMOVEDOCUMENT @l_xmlout

In the above code I need to map the ID and TimeStamp from the XML to Table. If it fails I need the failed records.

So my problem is when I use Time stamp in the with table it gives me new Timestamp so the comparision fails,

So I get the time stamp to varchar but still I could not compare the records.

I need the recors which doest match

RESULT SHOULD BE LIKE THIS.
---------------------------

ID            TIMESTAMP
1             0x0000000000580AA4

Because it fails from matching with the main table.

Karthik

beyondrelational
Starting Member

6 Posts

Posted - 2009-07-21 : 13:03:17

If you are on sql server 2005, this might help:

select XL.ID,XL.Timestamp
from openxml(@l_xmlout,'/root/subnode',2 )
with
(
ID int,
[Timestamp] VARCHAR(100)
)as XL inner join t1 on t1.ID = XL.ID
and XL.Timestamp = sys.fn_varbintohexstr(t1.Timestamp)

http://blog.beyondrelational.com

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-07-21 : 13:38:58

quote:
Originally posted by beyondrelational

If you are on sql server 2005, this might help:

select XL.ID,XL.Timestamp
from openxml(@l_xmlout,'/root/subnode',2 )
with
(
ID int,
[Timestamp] VARCHAR(100)
)as XL inner join t1 on t1.ID = XL.ID
and XL.Timestamp = sys.fn_varbintohexstr(t1.Timestamp)

http://blog.beyondrelational.com

Thanks.... Thanks a lot man.

I got the result. by using sys.fn_varbintohexstr()

Karthik

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources