How to query for Xml values and attributes from table in SQL Server

Question

I have the xml code as below,I want to get the "processEngine -> id" how can I get that using sql

"<?xml version="1.0" encoding="UTF-8"?>
<processEngine id="5077000" instanceName="bg2-dev.excers">
   <controller heartBeat="2014-10-27T15:59:50"/>
   <loader heartBeat="2014-10-27T16:01:00" queueLength="62"/>
   <conditionWaitList queueLength="52"/>
   <retryWaitList queueLength="0"/>
   <actionWaitList queueLength="0"/>
   <PreConditionPipelineManager load="1.463905239430332E-6" noOfPipelines="5" queueLength="0"
                                recentLoad="1.1947981003500136E-5">
      <pipeline heartBeat="2014-10-27T16:01:01" index="4" load="7.216747673537921E-6"
                name="Pre Condition Pipeline 0"
                recentLoad="5.963712649181781E-5"
                runTime="6811"
                running="false"
                startTime="2014-10-16T17:59:18"/>
</processEngine>"

Thanks in advance.......

Krishnraj Rana · Accepted Answer · 2014-10-27 11:08:34Z

1

Try this -

DECLARE @temp1 XML

SELECT @temp1 = '<?xml version="1.0" encoding="UTF-8"?>
<processEngine id="5077000" instanceName="bg2-dev.excers">
  <controller heartBeat="2014-10-27T15:59:50" />
  <loader heartBeat="2014-10-27T16:01:00" queueLength="62" />
  <conditionWaitList queueLength="52" />
  <retryWaitList queueLength="0" />
  <actionWaitList queueLength="0" />
  <pipeline heartBeat="2014-10-27T16:01:01" index="4" load="7.216747673537921E-6" name="Pre Condition Pipeline 0" recentLoad="5.963712649181781E-5" runTime="6811" running="false" startTime="2014-10-16T17:59:18" />
</processEngine>'

SELECT
    a.b.value('@id', 'varchar(10)') value
FROM
    @temp1.nodes('/processEngine') a(b)

answered Oct 27, 2014 at 11:08

Krishnraj Rana

6,6483 gold badges33 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

steve Over a year ago

Thanks for the response,I am not able to declare like @temp1 XML,I am getting error

steve Over a year ago

when I excecute this in sql server,it is showing error at line 1 only no additional info is displaying

Krishnraj Rana Over a year ago

Which version of sql server you are using ? because i have tested above code in SQL Server 2008 and its running fine...

Dan Guzman Over a year ago

What version of SQL Server are you running? The XML data type was introduced in SQL Server 2005.

steve Over a year ago

I am using sql server 2008 R2,I am executing this query in sql developer Version 4.0.1.14

Collectives™ on Stack Overflow

How to query for Xml values and attributes from table in SQL Server

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related