Beginning XML Databases (Wrox Beginning Guides)
| | ||
| | ||
| | ||
A special function is available in SQL Server called OPENXML . The OPENXML function can be used to access an XML data type as if it were a set of relational tables. The OPENXML function has to be specifically coded, and is a little complex, but it could perhaps be useful.
This query uses the FOR XML clause to find all regions :
SELECT * FROM region FOR XML AUTO, ELEMENTS GO
This is a partial result showing an XML structure for regions:
<region> <region_id>1</region_id> <region>Africa</region> <population>789548670</population> <area>26780325</area> </region> <region> <region_id>2</region_id> <region>Asia</region> <population>47382633</population> <area>657741</area> </region> ... <region> <region_id>13</region_id> <region>South America</region> <population>375489788</population> <area>17545171</area> </region>
The OPENXML function can be used to map from an XML structure back to a relational table structure. In other words, you can convert from an XML hierarchy to a flat relational structure. The following is some example code using the OPENXML function:
DECLARE @handle int DECLARE @xmlstr varchar(5000) SET @xmlstr =(SELECT region_id, region, population, area FROM region FOR XML AUTO, ROOT, ELEMENTS) SELECT @xmlstr EXEC sp_xml_preparedocument @handle OUTPUT, @xmlstr SELECT region_id, region, population, area FROM OPENXML(@handle, '/root/region', WITH Region GO
Figure 6-5 shows the result. Essentially the data is output in records, as shown here:
REGION_ID REGION POPULATION AREA --------- ------------------ ---------- -------- 1 Africa 789548670 26780325 2 Asia 47382633 657741 3 Australasia 24340222 7886602 4 Caribbean 40417697 268857 5 Central America 142653392 2360325 6 Europe 488674441 4583335 7 Far East 2100636517 15357441 8 Middle East 294625718 6798768 9 Near East 1499157105 4721322 10 North America 331599508 18729272 11 Oceania 9133256 536238 12 Russian Federation 258037209 21237500 13 South America 375489788 17545171
| | ||
| | ||
| | ||