开发者

timestamp to XML and then from XML, how?

people! i need some help in this I have a TABLE with timestamp field and i want to use WCF service to transfer data into other SQL Server Now, I try to simulate this, using "for xml"

DECLARE @aa XML
DECLARE @idoc int
SET @aa =(select * from db_table for xml auto, elements, root('root'), type)

select @aa returns

<root>
<Person>
 <preson_id>1</preson_id>
 <Name>Иван</Name>
 <Surname>Иванов</Surname>
 <BurthDate>1915-05-03</BurthDate>
 <guid>2E739E87-3CA4-4ED8-ADD0-8B59957668B8</guid>
 <version>AAAAAAAAB9E=</version>
</Person>
<Person>
 <preson_id>2</preson_id>
 <Name>Николай</Name>
 <Surname>Николаев</Surname>
 <BurthDate>2005-03-05</BurthDate>
 <guid>BDC41C59-D70F-4B70-954E-4918B9516AF8</guid>
 <version>AAAAAAAAB9I=</version>
</Person>
<Person>
 <preson_id>3</preson_id>
 <Name>Максим</Name>
 <Surname>Максимов</Surname>
 <BurthDate>1845-11-15</BurthDate>
 <guid>740E57F3-56BA-48B8-92AF-978D7B1D2712</guid>
 <version>AAAAAAAAB9M=</version>
</Person>
</开发者_JS百科root>

It's look good for the first look, but when i try to take data from xml, timestamp values looks not good(

EXEC sp_xml_preparedocument @idoc OUTPUT, @aa
SELECT * FROM OPENXML (@idoc, '/root/Person',2)
   WITH db_table

How can i take from xml timestamp values in source look?

Sorry for my not good English)))


If you use MS SQL Server 2005 or later you can do this instead

select
    r.p.value('preson_id[1]', 'int') as preson_id,
    r.p.value('BurthDate[1]', 'date') as BurthDate,
    r.p.value('guid[1]', 'uniqueidentifier') as guid,
    r.p.value('version[1]', 'rowversion') as timestamp
from @aa.nodes('root/Person') r(p)
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜