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'