XML data in SQL Server

[Cammc28]

Hi there

I am attempting to extract xml data out of a table on a SQL Server database. The xml is stored in a table column as a text value. The data is stored like this in the coulmn:

<DATA>

<SERVICE_9_ID>1234567</SERVICE_9_ID>

<SERVICE_9_TEAM lookup="1">TEAM_POT</SERVICE_9_TEAM>

<SERVICE_9_STAFF lookup="1">MACDONALD</SERVICE_9_STAFF >

<SERVICE_9_ TEAM lookup="1">TEAM_PHYSIO</SERVICE_9_STAFF_TEAM>

<SERVICE_9_ TEAM lookup="1">TEAM_DDT</SERVICE_9_STAFF_TEAM>

<SERVICE_9_ TEAM lookup="1">TEAM_DCC</SERVICE_9_STAFF_TEAM>

<SERVICE_9_STAFF lookup="1">MCLELLAN</SERVICE_9_STAFF>

<SERVICE_9_STAFF lookup="1">WILKINS</SERVICE_9_STAFF>

<SERVICE_9_STAFF lookup="1">SAVAGE</SERVICE_9_STAFF>

</DATA>

Currently I am treating the xml as a large string and stripping out the data I want using substring and charindex's as there are tag names that repeat. I use the charindex's to dictate where the next substring is to start so I can get the second, third, and fourth instances of each of these tags. As you can imagine, my query is comically large, almost 800 lines. Using this technique I am getting the desired results, but the size of the query is makes it very cumbersome and difficult to manage.

This is some of the code I am using, just so you know where I'm at:

SELECT
SUBSTRING(
test.dbo.idifd_form.xmlFormData,
CHARINDEX(
'>',
test.dbo.idifd_form.xmlFormData,
CHARINDEX(
'ID',
test.dbo.idifd_form.xmlFormData
)
)+1,
CHARINDEX(
'<',
test.dbo.idifd_form.xmlFormData,
CHARINDEX(
'ID',
test.dbo.idifd_form.xmlFormData
)
)
- CHARINDEX(
'>',
test.dbo.idifd_form.xmlFormData,
CHARINDEX(
'ID',
test.dbo.idifd_form.xmlFormData
)
)-1
) AS ID,

SUBSTRING(
test.dbo.idifd_form.xmlFormData,
CHARINDEX(
'>',
test.dbo.idifd_form.xmlFormData,
CHARINDEX(
'TEAM',
test.dbo.idifd_form.xmlFormData
)
)+1,
CHARINDEX(
'<',
test.dbo.idifd_form.xmlFormData,
CHARINDEX(
'TEAM',
test.dbo.idifd_form.xmlFormData
)
)
- CHARINDEX(
'>',
test.dbo.idifd_form.xmlFormData,
CHARINDEX(
'TEAM',
test.dbo.idifd_form.xmlFormData
)
)-1
) AS TEAM, ...

This goes on, and on, and on...

Does anyone know of a technique in MS SQL Server 2000/2005 which can extract this data any easier than a giant substring query? I am reasonably inexperienced with SQL Server and any help will be much appreciated.

[debasisdas]

As you have posted a question in the articles section it is being moved to SQL Server Forum.

MODERATOR.