SQL Server - from xml flat to tabular data

This is a quick tips to show you the simplest way to convert a set of xml data into the equivalent tabular representation.

The data is taken from the adventurework2008 database sample.

Under this database, execute the script below :

SELECT * FROM SALES.INDIVIDUAL

The column Demographics has xml as type. The content looks like :

<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
  <TotalPurchaseYTD>8248.99</TotalPurchaseYTD>
  <DateFirstPurchase>2001-07-22Z</DateFirstPurchase>
  <BirthDate>1966-04-08Z</BirthDate>
  <MaritalStatus>M</MaritalStatus>
  <YearlyIncome>75001-100000</YearlyIncome>
  <Gender>M</Gender>
  <TotalChildren>2</TotalChildren>
  <NumberChildrenAtHome>0</NumberChildrenAtHome>
  <Education>Bachelors </Education>
  <Occupation>Professional</Occupation>
  <HomeOwnerFlag>1</HomeOwnerFlag>
  <NumberCarsOwned>0</NumberCarsOwned>
  <CommuteDistance>1-2 Miles</CommuteDistance>
</IndividualSurvey>

To translate this result into tabular data, let's see the query below :

WITH XMLNAMESPACES(DEFAULT
    'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey')
SELECT DEMOGRAPHICS.query('data(/IndividualSurvey/TotalPurchaseYTD)') as TotalPurchaseYTD       
       ,DEMOGRAPHICS.query('data(/IndividualSurvey/BirthDate)') as BirthDate
       ,DEMOGRAPHICS.query('data(/IndividualSurvey/MaritalStatus)') as MaritalStatus
       ,DEMOGRAPHICS.query('data(/IndividualSurvey/YearlyIncome)') as YearlyIncome
       ,DEMOGRAPHICS.query('data(/IndividualSurvey/Gender)') as Gender
       ,DEMOGRAPHICS.query('data(/IndividualSurvey/TotalChildren)') as TotalChildren
       ,DEMOGRAPHICS.query('data(/IndividualSurvey/NumberChildrenAtHome)') as NumberChildrenAtHome
       ,DEMOGRAPHICS.query('data(/IndividualSurvey/Education)') as Education
       ,DEMOGRAPHICS.query('data(/IndividualSurvey/Occupation)') as Occupation
       ,DEMOGRAPHICS.query('data(/IndividualSurvey/HomeOwnerFlag)') as HomeOwnerFlag
       ,DEMOGRAPHICS.query('data(/IndividualSurvey/NumberCarsOwned)') as NumberCarsOwned
       ,DEMOGRAPHICS.query('data(/IndividualSurvey/CommuteDistance)') as CommuteDistance
       FROM SALES.INDIVIDUAL

Since there is a xml namespace declared in the xml, we have to declare it by using :
WITH XMLNAMESPACES.

In the XQuery, we locate each element through their hierarchical order.In the xml result, we can see that the IndividualSurvey is the root of each xml data set. All the others elements come under this hierachy level.
Although this query is very simple to write, its drawback is about the performance.
I'll show you in a next trick how to improve and optimize this query to make the most of the XQuery

11/11/11 11:11 - un peu de souffle

11/11/11 11:11 - boffffff onze s''en fout !!!!