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>1 234567</SERVICE_9_ID>
<SERVICE_9_TE AM lookup="1">TEAM _POT</SERVICE_9_TEAM>
<SERVICE_9_STAF F lookup="1">MACD ONALD</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_STAF F lookup="1">MCLE LLAN</SERVICE_9_STAFF >
<SERVICE_9_STAF F lookup="1">WILK INS</SERVICE_9_STAFF >
<SERVICE_9_STAF F lookup="1">SAVA GE</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.xmlFormDat a,
CHARINDEX(
'>',
test.dbo.idifd_ form.xmlFormDat a,
CHARINDEX(
'ID',
test.dbo.idifd_ form.xmlFormDat a
)
)+1,
CHARINDEX(
'<',
test.dbo.idifd_ form.xmlFormDat a,
CHARINDEX(
'ID',
test.dbo.idifd_ form.xmlFormDat a
)
)
- CHARINDEX(
'>',
test.dbo.idifd_ form.xmlFormDat a,
CHARINDEX(
'ID',
test.dbo.idifd_ form.xmlFormDat a
)
)-1
) AS ID,
SUBSTRING(
test.dbo.idifd_ form.xmlFormDat a,
CHARINDEX(
'>',
test.dbo.idifd_ form.xmlFormDat a,
CHARINDEX(
'TEAM',
test.dbo.idifd_ form.xmlFormDat a
)
)+1,
CHARINDEX(
'<',
test.dbo.idifd_ form.xmlFormDat a,
CHARINDEX(
'TEAM',
test.dbo.idifd_ form.xmlFormDat a
)
)
- CHARINDEX(
'>',
test.dbo.idifd_ form.xmlFormDat a,
CHARINDEX(
'TEAM',
test.dbo.idifd_ form.xmlFormDat a
)
)-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.
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>1 234567</SERVICE_9_ID>
<SERVICE_9_TE AM lookup="1">TEAM _POT</SERVICE_9_TEAM>
<SERVICE_9_STAF F lookup="1">MACD ONALD</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_STAF F lookup="1">MCLE LLAN</SERVICE_9_STAFF >
<SERVICE_9_STAF F lookup="1">WILK INS</SERVICE_9_STAFF >
<SERVICE_9_STAF F lookup="1">SAVA GE</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.xmlFormDat a,
CHARINDEX(
'>',
test.dbo.idifd_ form.xmlFormDat a,
CHARINDEX(
'ID',
test.dbo.idifd_ form.xmlFormDat a
)
)+1,
CHARINDEX(
'<',
test.dbo.idifd_ form.xmlFormDat a,
CHARINDEX(
'ID',
test.dbo.idifd_ form.xmlFormDat a
)
)
- CHARINDEX(
'>',
test.dbo.idifd_ form.xmlFormDat a,
CHARINDEX(
'ID',
test.dbo.idifd_ form.xmlFormDat a
)
)-1
) AS ID,
SUBSTRING(
test.dbo.idifd_ form.xmlFormDat a,
CHARINDEX(
'>',
test.dbo.idifd_ form.xmlFormDat a,
CHARINDEX(
'TEAM',
test.dbo.idifd_ form.xmlFormDat a
)
)+1,
CHARINDEX(
'<',
test.dbo.idifd_ form.xmlFormDat a,
CHARINDEX(
'TEAM',
test.dbo.idifd_ form.xmlFormDat a
)
)
- CHARINDEX(
'>',
test.dbo.idifd_ form.xmlFormDat a,
CHARINDEX(
'TEAM',
test.dbo.idifd_ form.xmlFormDat a
)
)-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.
Comment