SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 XML column in SQL Table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

KatiSQL
Starting Member

3 Posts

Posted - 07/22/2013 :  11:01:57  Show Profile  Reply with Quote
Hi

I have a table named(requestpropertydata) which one of its columns(propertyvalue) has XML detail like this
<Absence>
<date_from>2013-08-19T00:00:00+01:00</date_from>
<date_to>2013-08-19T00:00:00+01:00</date_to>
<absence_type>AAWS</absence_type>
<represents>S</represents>

I need to write a transaction to get individual data out of this column. Something like this

select propertyvalue.date_from.value() from requestpropertydata

Would you please help me ?

Thank you

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 07/22/2013 :  11:07:03  Show Profile  Reply with Quote
Like this:
SELECT
	YourOtherColumns,	
	c.value('date_from[1]','datetime')
FROM
	requestpropertydata y
	CROSS APPLY 
		y.propertyvalue.nodes('/Absence') T(c)

Edited by - James K on 07/22/2013 11:07:26
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/22/2013 :  11:07:59  Show Profile  Reply with Quote
you can do something like


declare @x table
(
x xml
)

insert @x
values
('<Absence>
<date_from>2013-08-19T00:00:00+01:00</date_from>
<date_to>2013-08-19T00:00:00+01:00</date_to>
<absence_type>AAWS</absence_type>
<represents>S</represents></Absence>')


SELECT t.u.value('(./date_from)[1]','datetime') as date_from,
t.u.value('(./date_to)[1]','datetime') as date_to,
t.u.value('(./absence_type)[1]','varchar(15)') as absence_type,
t.u.value('(./represents)[1]','varchar(5)') as represents
FROM @x t1
CROSS APPLY x.nodes('Absence')t(u)


output
----------------------------------
date_from	date_to	absence_type	represents
2013-08-18 23:00:00.000	2013-08-18 23:00:00.000	AAWS	S


NB:- I've updated your XML value to make it well formed (it was missing closing tag for Absence)




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Edited by - visakh16 on 07/22/2013 11:15:17
Go to Top of Page

KatiSQL
Starting Member

3 Posts

Posted - 07/22/2013 :  11:12:41  Show Profile  Reply with Quote
Thank you for quick reply

I have run it but I have got error message
The XMLDT method 'nodes' can only be invoked on columns of type xml.

I have checked the column and the type is varchar.

I can not change the data structure so any idea how to get the value??

Cheers
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/22/2013 :  11:17:18  Show Profile  Reply with Quote
quote:
Originally posted by KatiSQL

Thank you for quick reply

I have run it but I have got error message
The XMLDT method 'nodes' can only be invoked on columns of type xml.

I have checked the column and the type is varchar.

I can not change the data structure so any idea how to get the value??

Cheers


just a small modification so far as the value inside it is a well formed XML (your posted value was not!)


declare @x table
(
x varchar(8000)
)

insert @x
values
('<Absence>
<date_from>2013-08-19T00:00:00+01:00</date_from>
<date_to>2013-08-19T00:00:00+01:00</date_to>
<absence_type>AAWS</absence_type>
<represents>S</represents></Absence>')


SELECT t.u.value('(./date_from)[1]','datetime') as date_from,
t.u.value('(./date_to)[1]','datetime') as date_to,
t.u.value('(./absence_type)[1]','varchar(15)') as absence_type,
t.u.value('(./represents)[1]','varchar(5)') as represents
FROM (SELECT CAST(x AS xml) AS x FROM @x) t1
CROSS APPLY x.nodes('Absence')t(u)





output
----------------------------------
date_from	date_to	absence_type	represents
2013-08-18 23:00:00.000	2013-08-18 23:00:00.000	AAWS	S


NB:- I've updated your XML value to make it well formed (it was missing closing tag for Absence)



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

KatiSQL
Starting Member

3 Posts

Posted - 07/22/2013 :  12:36:05  Show Profile  Reply with Quote
Thank you very much
It works
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/22/2013 :  13:05:22  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000