how to read xml variable using openxml

Question

Post reply

how to read xml variable using openxml

prabhu.st

SSCrazy

Points: 2552
More actions
December 14, 2012 at 9:28 am

#267436

Hi,
I have a xml like this.
<DocumentElement>
<tLockhistory>
<lockrequesteddate>2012-12-14T00:00:00+05:30</lockrequesteddate>
<lockexpirydate>2012-12-29T00:00:00+05:30</lockexpirydate>
<createddate>2012-12-14T00:00:00+05:30</createddate>
</tLockhistory>
<tLockhistory>
<lockrequesteddate>2012-12-14 00:00:00</lockrequesteddate>
<lockexpirydate>2012-12-29 00:00:00</lockexpirydate>
<createddate>2012-12-14 00:00:00</createddate>
</tLockhistory>
</DocumentElement>
output is this.
lockrequesteddate lockexpirydate
----------------------- -----------------------
2012-12-13 18:30:00.000 2012-12-28 18:30:00.000
2012-12-14 00:00:00.000 2012-12-29 00:00:00.000
Note:
first row is the one really passed from the XML,
second row was added by me to check where is the issue.
My Question is:
How can I handle the XML date values, where the system reads a date value as a value which actually one day previous to the given date.
any immediate suggestion would be a great help to me.
Thanks in advance.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

jos.moy SSC Rookie Points: 28 More actions · Answer 1

Hola,

No entiendo bien tu pregunta, espero te pueda ayudar esto, no hablo ni escribo bien el ingles

select

campo.value('(/Encabezado/Heads_dependiendo_arbol_del_XML)[1]','tipo_dato'')

from tabla

y de ahi ya podrias manejar tu fecha a tu gusto, adicional, te recomentaria crear una #temp para mejor manejo.

Saludos

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

prabhu.st (12/14/2012)
Hi,
I have a xml like this.
<DocumentElement>
<tLockhistory>
<lockrequesteddate>2012-12-14T00:00:00+05:30</lockrequesteddate>
<lockexpirydate>2012-12-29T00:00:00+05:30</lockexpirydate>
<createddate>2012-12-14T00:00:00+05:30</createddate>
</tLockhistory>
<tLockhistory>
<lockrequesteddate>2012-12-14 00:00:00</lockrequesteddate>
<lockexpirydate>2012-12-29 00:00:00</lockexpirydate>
<createddate>2012-12-14 00:00:00</createddate>
</tLockhistory>
</DocumentElement>
output is this.
lockrequesteddate lockexpirydate
----------------------- -----------------------
2012-12-13 18:30:00.000 2012-12-28 18:30:00.000
2012-12-14 00:00:00.000 2012-12-29 00:00:00.000
Note:
first row is the one really passed from the XML,
second row was added by me to check where is the issue.
My Question is:
How can I handle the XML date values, where the system reads a date value as a value which actually one day previous to the given date.
any immediate suggestion would be a great help to me.
Thanks in advance.

The first time above is in UTC time. It is the time on the system expressed with an offset. If you add the offset to the time displayed (2012-12-13 18:30:00.000 + 5:30) that will be 2012-12-14 00:00:00.000.

Other than that, I can't be much help as I am learning XML and XML processing myself.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 3

jos.moy (12/14/2012)
Hola,
No entiendo bien tu pregunta, espero te pueda ayudar esto, no hablo ni escribo bien el ingles
select
campo.value('(/Encabezado/Heads_dependiendo_arbol_del_XML)[1]','tipo_dato'')
from tabla
y de ahi ya podrias manejar tu fecha a tu gusto, adicional, te recomentaria crear una #temp para mejor manejo.
Saludos

Gosh... it would really be nice if I could actually read this. Would you mind replying in English, please?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jos.moy SSC Rookie Points: 28 More actions · Answer 4

Hello

In a SQL to get the fields of XML to SQL query would be like this, not if you really want to take SQL.

select

campo.value('(/Encabezado/Heads_tree_XML)[1]','Data_type'')

from tabla

I hope to help you

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 5

jos.moy (12/14/2012)
Hello
In a SQL to get the fields of XML to SQL query would be like this, not if you really want to take SQL.
select
campo.value('(/Encabezado/Heads_tree_XML)[1]','Data_type'')
from tabla
I hope to help you

Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mister.magoo SSC-Forever Points: 47068 More actions · Answer 6

As you don't mention exactly what you are trying to achieve, it is hard to give a definite answer, but my guess from your desire to see the date as the 14th, not the 13th is that you want to show it as local time for the +05:30 time zone, in which case, just ignore the timezone...

declare @xml xml ='<DocumentElement>

<tLockhistory>

<lockrequesteddate>2012-12-14T00:00:00+05:30</lockrequesteddate>

<lockexpirydate>2012-12-29T00:00:00+05:30</lockexpirydate>

<createddate>2012-12-14T00:00:00+05:30</createddate>

</tLockhistory>

<tLockhistory>

<lockrequesteddate>2012-12-14 00:00:00</lockrequesteddate>

<lockexpirydate>2012-12-29 00:00:00</lockexpirydate>

<createddate>2012-12-14 00:00:00</createddate>

</tLockhistory>

</DocumentElement> '

-- selecting the value as CHAR(19) will take the date and time, but drop the timezone information, giving you the "local" time.

select cast(nd.value('(./lockrequesteddate/text())[1]','char(19)') as datetime) as lockrequesteddate

from @xml.nodes('/DocumentElement/tLockhistory') as x(nd)

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

prabhu.st SSCrazy Points: 2552 More actions · Answer 7

Hi all,

thanks for your spending your finest time, I am sorry about "not giving my question clearly", but I have got some valuable tips from your replies that to avoid the Time Zone.

Thanks again for your time.

--Prabhu.st