XML is needed here I think - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

XML is needed here I think

Author

Topic

craigwg
Posting Yak Master

154 Posts

Posted - 2010-12-07 : 16:21:21

Have a table that has a column that looks like this:


<root><Option><PostitionTitle>Telesales Consultants</PostitionTitle><Status>Full-time</Status><ReportTo>Team Leader</ReportTo><JobSummary>Sales of general insurance products over the phone.</JobSummary><Duties>Make calls to acquire new customers
Convert effective calls to sales
Update contact management database

</Duties><Knowledge>Speak good English
Computer literate
Must have insurance certification such as :
- Certificate in General Insurance or
- Health Insurance Certificate
</Knowledge><Credentials>Nil</Credentials><SpecialRequirements>Must not be a practising insurance consultant</SpecialRequirements></Option></root>

I have 36 rows in my table that I can access using this query:

SELECT  convert(xml,content_html)
  FROM [webportalv60].[dbo].[content] c
  JOIN JobSearch..jobs_old jo ON jo.contentID = c.content_id
  WHERE end_date IS NULL OR end_date >= GETUTCDATE()

I need to parse that data out and get to insert each peice into a row. How do I do that?

Craig Greenwood

itsupport
Starting Member

5 Posts

Posted - 2010-12-08 : 04:07:49

To parse out the data & insert each piece into row you can use the the Instr() function into the query. After that you have to use Left() and Mid() values.

Regards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources