Extract XML Data with TSQL from duplicate tags
-
Hi All
I am trying to extract data from an XML File (contained in a string column in a table) as follows:
<
Medications>
<Medication>
<Description>Medicine1</Description>
<Route>ORAL</Route>
<Dose>100ml</Dose>
<Frequency>DAILY</Frequency>
<Duration>twice</Duration>
</Medication>
<Medication>
<Description>Medicine2</Description>
<Route>INHALE</Route>
<Dose>200mg</Dose>
<Frequency>WEEK</Frequency>
<Duration>1</Duration>
</Medication>
<Medication>
<Description>Medicine3</Description>
<Route>REC</Route>
<Dose>10mg</Dose>
<Frequency>Q1H</Frequency>
<Duration>2 Months</Duration>
</Medication>
<Medication>
<Description>Medicine4</Description>
<Route>EYE</Route>
<Dose>10ml</Dose>
<Frequency>WEEK</Frequency>
<Duration>1</Duration>
</Medication>
<Medication>
<Description>Medicine5</Description>
<Route>IM</Route>
<Dose>10ml</Dose>
<Frequency>MONTH</Frequency>
<Duration>1</Duration>
</Medication>
</Medications>
The problem is that the tags names (medication) are the same. How do I retrieve the data in the SELECT from duplicate tag names?
------------------
DECLARE @idocS int
DECLARE @docS varchar(8000)
SELECT @docS = Object from ServiceOrderObject where ServiceOrderID = 24
SET @docS = REPLACE (@docS, '<?xml version="1.0" encoding="utf-16"?>', '')
EXEC sp_xml_preparedocument @idocS OUTPUT, @docS
SELECT * FROM FROM OPENXML (@idocS, 'Medications/Medication', 1)
WITH (Medication varchar(50) '../Medications/Medication')
Help will be appreciated
-
try this:
DECLARE @n nvarchar(4000)
SET @n='
<Medications>
<Medication>
<Description>Medicine1</Description>
<Route>ORAL</Route>
<Dose>100ml</Dose>
<Frequency>DAILY</Frequency>
<Duration>twice</Duration>
</Medication>
<Medication>
<Description>Medicine2</Description>
<Route>INHALE</Route>
<Dose>200mg</Dose>
<Frequency>WEEK</Frequency>
<Duration>1</Duration>
</Medication>
<Medication>
<Description>Medicine3</Description>
<Route>REC</Route>
<Dose>10mg</Dose>
<Frequency>Q1H</Frequency>
<Duration>2 Months</Duration>
</Medication>
<Medication>
<Description>Medicine4</Description>
<Route>EYE</Route>
<Dose>10ml</Dose>
<Frequency>WEEK</Frequency>
<Duration>1</Duration>
</Medication>
<Medication>
<Description>Medicine5</Description>
<Route>IM</Route>
<Dose>10ml</Dose>
<Frequency>MONTH</Frequency>
<Duration>1</Duration>
</Medication>
</Medications>'
declare @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @n
SELECT * FROM OPENXML (@idoc, 'Medications/Medication', 1)
WITH (Description varchar(50) 'Description',
Duration varchar(50) 'Duration',
Route varchar(50) 'Route')
-
Medication is the parent in your xml string and does not have a value to outpt as a column in your recordset. This will return you all the info in your string.
SELECT *
FROM OPENXML (@idocS, 'Medications/Medication', 2)
WITH ([Description] varchar(100),
[Route] varchar(10),
[Dose] varchar(10),
[Frequency] varchar(10),
[Duration] varchar(10))
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply