• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 292
  • Last Modified:

Extract data from XML field in SQL Server 2005 table

I have a sql server table

CREATE TABLE [dbo].[XmlImportTest](

                [xmlFileName] [varchar](300) COLLATE Latin1_General_CI_AS NULL,

                [xml_data] [xml] NULL

) ON [PRIMARY]

which contains xml

<form name="esb_recording" application-name="esb_recording" application-id="102" document-id="441" producer="Penvision Formidable Application Server" export-time="2011-04-29T15:03:17+01:00" first-page="83.17.3.121" last-page="83.17.3.121">
  <submit penid="0x000001e3000214ed" penid-dashed="ATD-AAF-BUX-QE" first-stroke-time="2011-04-29T14:42:07+01:00" last-stroke-time="2011-04-29T14:43:42+01:00" total-time="0:01:35" email="cryan@futurerange.ie" phone="+353868795846" />
  <field type="boxed" name="site_code" normalized-score="0.512" resemblance-score="0.579">D0173TPI</field>
  <field type="boxed" name="mon_day_1" normalized-score="0.726" resemblance-score="0.747">4567895</field>
  <field type="boxed" name="tues_day_1" normalized-score="0.697" resemblance-score="0.816">6432567</field>
  <field type="boxed" name="wed_day_1" normalized-score="0.624" resemblance-score="0.64">8943245</field>
  <field type="boxed" name="thur_day_1" normalized-score="0.634" resemblance-score="0.554">6789432</field>
  <field type="boxed" name="fri_day_1" normalized-score="0.718" resemblance-score="0.898">5678943</field>
  <field type="boxed" name="sat_day_1" normalized-score="0.753" resemblance-score="0.843">2456789</field>
</form>

How can I extract say the field value for the site_code etc using SQL?
0
itwex
Asked:
itwex
1 Solution
 
HainKurtSr. System AnalystCommented:
try this:
declare @xml xml
select top 1 @xml=xml_data from XmlImportTest;

select 
Field.value('.', 'nvarchar(max)') v,
Field.value('(@normalized-score)[1]','nvarchar(max)') ns
from @xml.nodes('//form/field') Fields(Field)
where Field.value('(@name)[1]','nvarchar(max)')='site_code'

Open in new window

0
 
itwexAuthor Commented:
works fine thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now