CODEWITHSUNDEEP
sql-serverxml
steve
steve

Reputation: 664

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

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.......

Upvotes: 0

Views: 40

Answers (1)

Krishnraj Rana
Krishnraj Rana

Reputation: 6656

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)

Upvotes: 1

Related Questions