Generating Tuples from XML: OPENXML

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   

Figure 6-5: OPENXML converts from XML to relational tuples