Rita Byrne
asked on
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_reco rding" 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:4 2+01:00" total-time="0:01:35" email="cryan@futurerange.i e" 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</f ield>
<field type="boxed" name="tues_day_1" normalized-score="0.697" resemblance-score="0.816"> 6432567</f ield>
<field type="boxed" name="wed_day_1" normalized-score="0.624" resemblance-score="0.64">8 943245</fi eld>
<field type="boxed" name="thur_day_1" normalized-score="0.634" resemblance-score="0.554"> 6789432</f ield>
<field type="boxed" name="fri_day_1" normalized-score="0.718" resemblance-score="0.898"> 5678943</f ield>
<field type="boxed" name="sat_day_1" normalized-score="0.753" resemblance-score="0.843"> 2456789</f ield>
</form>
How can I extract say the field value for the site_code etc using SQL?
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_reco
<submit penid="0x000001e3000214ed"
<field type="boxed" name="site_code" normalized-score="0.512" resemblance-score="0.579">
<field type="boxed" name="mon_day_1" normalized-score="0.726" resemblance-score="0.747">
<field type="boxed" name="tues_day_1" normalized-score="0.697" resemblance-score="0.816">
<field type="boxed" name="wed_day_1" normalized-score="0.624" resemblance-score="0.64">8
<field type="boxed" name="thur_day_1" normalized-score="0.634" resemblance-score="0.554">
<field type="boxed" name="fri_day_1" normalized-score="0.718" resemblance-score="0.898">
<field type="boxed" name="sat_day_1" normalized-score="0.753" resemblance-score="0.843">
</form>
How can I extract say the field value for the site_code etc using SQL?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER