Xml/Xslt - Read XML data using t-sql - Asked By Mayank Tripathi on 09-Jul-13 12:54 AM

Dear Friends,

I am facing an issue with reading data from XML field using T-SQL.
Below is the sample code....
Error : Incorrect syntax near the keyword 'CONVERT'.
Please note : In table InsertXML , field XMLData is of NTEXT type and not XML.

Please help to get rid of this.


DECLARE @XMLdata XML
SET @XMLdata=N'<users>
      <user>
           <FirstName>Suresh</FirstName>
           <LastName>Dasari</LastName>
           <UserName>SureshDasari</UserName>
           <Job>Team Leader</Job>
      </user>
      <user>
           <FirstName>Mahesh</FirstName>
           <LastName>Dasari</LastName>
           <UserName>MaheshDasari</UserName>
           <Job>Software Developer</Job>
      </user>
      <user>
           <FirstName>Madhav</FirstName>
            <LastName>Yemineni</LastName>
           <UserName>MadhavYemineni</UserName>
           <Job>Business Analyst</Job>
      </user>
</users>'

CREATE TABLE InsertXML (ID INT IDENTITY(1,1), XMLdata NTEXT)

GO

INSERT INTO InsertXML (XMLData) SELECT CAST(@XMLdata AS NVARCHAR(MAX))

GO

SELECT * FROM InsertXML

SELECT
t.value('(FirstName/text())[1]','varchar(50)')AS FirstName ,
t.value('(LastName/text())[1]','varchar(50)')AS LastName,
t.value('(UserName/text())[1]','varchar(50)')AS UserName,
t.value('(Job/text())[1]','varchar(50)')AS Job
FROM InsertXML
CROSS APPLY CONVERT(XML, XMLdata).nodes('/users/user') AS TempTable(t)
WHERE t.value('(FirstName/text())[1]','varchar(50)') = 'Madhav'
Robbe Morris replied to Mayank Tripathi on 09-Jul-13 08:30 AM
I think you are making this harder than it needs to be:

http://www.nullskull.com/articles/20030627c.asp
Mayank Tripathi replied to Robbe Morris on 10-Jul-13 12:36 AM
Thanks Robbe,
But actually the table in Production is having a column with TEXT datatype, and stores XML data into that.
So cannot change that.
I just tried to simulate the issue facing while creating one logic, which need to deploy on Production soon.
Robbe Morris replied to Mayank Tripathi on 10-Jul-13 06:36 AM
If you alter the table and change TEXT to varchar(max), this will work.  Otherwise, you are right, you are hosed.