InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 05:51:01 GMT20http://www.sqlservercentral.com/Forums/Topic793276-21-1.aspxHi AviTo get the elements out of your XML you can use this:[code]DECLARE @xml XMLSELECT @xml = N'<Employee><EMPID>1</EMPID><EMPNAME>AAA</EMPNAME><EMPSALARY>2000.00</EMPSALARY><DEPTID>1</DEPTID></Employee>'SELECT T.C.value('(EMPID)[1]', 'int') ,T.C.value('(EMPNAME)[1]', 'nvarchar(20)') ,T.C.value('(EMPSALARY)[1]', 'decimal(15,5)') ,T.C.value('(DEPTID)[1]', 'int') FROM @xml.nodes('Employee') T(C)[/code]If you have to apply this to your table, have a look for CROSS APPLY with XML.GreetsFloFri, 06 Nov 2009 03:18:50 GMTFlorian Reischlhttp://www.sqlservercentral.com/Forums/Topic793276-21-1.aspxHello Expert,Same above scenario i have...i have a table in which one column xmldata(having datatype xml) contain data in xml format, as given below:<Employee> <EMPID>1</EMPID> <EMPNAME>AAA</EMPNAME> <EMPSALARY>2000.00</EMPSALARY> <DEPTID>1</DEPTID></Employee>Now i have to read this xml data and display it with respective column names..like [b]EMPID EMPNAME EMPSALARY DEPTID1 AAA 2000.00 1[/b]please help..Thanks in advance...reagrds,AviThu, 05 Nov 2009 23:42:10 GMTavnish26http://www.sqlservercentral.com/Forums/Topic793276-21-1.aspxThank you very much. It works. What a nice query!!!!You are great!!! (champs of SQL Server) :-)Thu, 24 Sep 2009 08:26:11 GMTParesh Dehadrayhttp://www.sqlservercentral.com/Forums/Topic793276-21-1.aspx[code]SELECT r.value('@Value','int')FROM JobCROSS APPLY JobXml.nodes('/Job/TimeID') AS x(r)[/code]Thu, 24 Sep 2009 08:18:17 GMTMark-101232http://www.sqlservercentral.com/Forums/Topic793276-21-1.aspxHello Sir/MadamI have following Xml in Job table which column name is JobXml (Xml datatype)<Job> <TimeID Value="2323"/> <TimeID Value="4445"/> <TimeID Value="3453"/> <TimeID Value="7677"/></Job>I want to write a select query who returns all TimeID Value like this2323444534537677Please give me the query to select these records. Currently I am using following query but it returns only one TimeID value:select JobXml.value('(/Job/TimeID/@Value)[1]','int') from JobPlease help me. Thanks in advance :-)Thu, 24 Sep 2009 07:54:57 GMTParesh Dehadray