How to read XML ntext type column

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)

How to read XML ntext type column

Author

Topic

emyk
Yak Posting Veteran

57 Posts

Posted - 2012-08-26 : 15:13:45

I have a column with XML data which is saved as ntext. I am trying to read some of the values from this column.

Here is a sample code that I am working on:
This code returns only one raw, but I need to modify this so that I can insert more data and be able to display multiple rows.


CREATE TABLE #test (

col1 NTEXT

)
 
INSERT #test

SELECT 

'<xmlF><FNumber type="int">1118</FNumber><AttachmentPath type="string" /><RequesterId type="int">232</RequesterId><Requester type="string">John Smith</Requester><RequestDate type="DateTime">3/24/2008 11:23:27 AM</RequestDate></xmlF>'
--FOR TESTING I Need to insert more rows and be able to diplay more that one row.
--This is where I need some direction.
--'<xmlF><FNumber type="int">2423</FNumber><AttachmentPath type="string" /><RequesterId type="int">232</RequesterId><Requester type="string">Alex Haile</Requester><RequestDate type="DateTime">3/24/2008 11:23:27 AM</RequestDate></xmlF>'
--'<xmlF><FNumber type="int">2423</FNumber><AttachmentPath type="string" /><RequesterId type="int">232</RequesterId><Requester type="string">Alex Haile</Requester><RequestDate type="DateTime">3/24/2008 11:23:27 AM</RequestDate></xmlF>'



 

DECLARE @nd XML

SET @nd = (SELECT CONVERT(XML, col1)

FROM #test)

 

SELECT @nd.value('(/xmlF/FNumber)[1]', 'nvarchar(100)') As FNumber,

@nd.value('(/xmlF/RequesterId)[1]', 'nvarchar(100)') As RequesterId,

@nd.value('(/xmlF/Requester)[1]', 'nvarchar(100)') As Requester, 

@nd.value('(/xmlF/RequestDate)[1]', 'nvarchar(100)') As RequestDate

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-26 : 15:27:49

[code]
CREATE TABLE #test (

col1 NTEXT

)

INSERT #test

SELECT '<xmlF><FNumber type="int">1118</FNumber><AttachmentPath type="string" /><RequesterId type="int">232</RequesterId><Requester type="string">John Smith</Requester><RequestDate type="DateTime">3/24/2008 11:23:27 AM</RequestDate></xmlF>' union all
SELECT '<xmlF><FNumber type="int">2423</FNumber><AttachmentPath type="string" /><RequesterId type="int">232</RequesterId><Requester type="string">Alex Haile</Requester><RequestDate type="DateTime">3/24/2008 11:23:27 AM</RequestDate></xmlF>' union all
SELECT '<xmlF><FNumber type="int">2423</FNumber><AttachmentPath type="string" /><RequesterId type="int">232</RequesterId><Requester type="string">Alex Haile</Requester><RequestDate type="DateTime">3/24/2008 11:23:27 AM</RequestDate></xmlF>'

SELECT m.v.value('(/xmlF/FNumber)[1]', 'nvarchar(100)') As FNumber,

m.v.value('(/xmlF/RequesterId)[1]', 'nvarchar(100)') As RequesterId,

m.v.value('(/xmlF/Requester)[1]', 'nvarchar(100)') As Requester,

m.v.value('(/xmlF/RequestDate)[1]', 'nvarchar(100)') As RequestDate
FROM (SELECT CAST(col1 as xml) as col1 from #test)t
CROSS APPLY col1.nodes('/xmlF')m(v)

output
-------------------------------------------------------------------------------------
FNumber RequesterId Requester RequestDate
---------------------------------------------------------
1118 232 John Smith 3/24/2008 11:23:27 AM
2423 232 Alex Haile 3/24/2008 11:23:27 AM
2423 232 Alex Haile 3/24/2008 11:23:27 AM

[/code]

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

emyk
Yak Posting Veteran

57 Posts

Posted - 2012-08-26 : 16:03:08

That is perferct.

thank you!
Alex

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-26 : 16:27:40

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources