September 19, 2008 at 4:47 pm
I'm trying to use OPENXML to retrieve values from XML data.
In the sample below (I replaced <> with () ) I am trying to extract LocationID, Country, Region and City. There is one record, but it has two City values. When I use OPENXML I get one record returned with one City value.
How can I extract the all the City values for this record? Is there a way to extract this data into two records (one for each City value) where LocationID, Country and Region have the same values for each City?
DECLARE @XMLData XML
SET @XMLData =
'(tz_data)
(Location)
(LocationID)AT</LocationID)
(Country)Austria</Country)
(Region)(/Region)
(Landmarks)
(City)Vienna(/City)
(City)Salzburg(/City)
(/Landmarks)
(/Location)
(/tz_data)'
DECLARE @XmlDocumentHandle int
EXEC sp_xml_preparedocument @XmlDocumentHandle OUTPUT, @XMLData
SELECT LocationID, Country, Region, City
FROM OPENXML (@XmlDocumentHandle, '/tz_data/Location',2)
WITH (LocationID char(10) 'LocationID',
Country char(30) 'Country',
Region char(30) 'Region',
City char(50) 'Landmarks/City')
EXEC sp_xml_removedocument @XmlDocumentHandle
September 19, 2008 at 5:29 pm
I found syntax that works
SELECT *
FROM OPENXML (@XmlDocumentHandle, '/tz_data/Location/Landmarks/City',2)
WITH ( LocationID char(10) '../../LocationID',
Country char(30) '../../Country',
Region char(30) '../../Region',
City char(50) '.')
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply