SQL Server XML Data Type – How to Query with Examples

sql serversql server 2014xml

I know this question might have already been asked, but I am still trying to figure it out how to query the xml data and I really need an example.

I have the following XML code that is inside a variable called @x in sql server:

DECLARE @X XML


SELECT @X = 
'<SizeGuide title="Baby - All">
  <Headers>
    <HTitle />
    <HBase IsLetteredSizes="0">
      <HCols title="Boden Size">
        <HCol>
          <HColId>1</HColId>
          <HValue>newborn</HValue>
        </HCol>
        <HCol>
          <HColId>2</HColId>
          <HValue>0-3m</HValue>
        </HCol>
        <HCol>
          <HColId>3</HColId>
          <HValue>3-6m</HValue>
        </HCol>
        <HCol>
          <HColId>4</HColId>
          <HValue>6-12m</HValue>
        </HCol>
        <HCol>
          <HColId>5</HColId>
          <HValue>12-18m</HValue>
        </HCol>
        <HCol>
          <HColId>6</HColId>
          <HValue>18-24</HValue>
        </HCol>
        <HCol>
          <HColId>7</HColId>
          <HValue>2-3y</HValue>
        </HCol>
      </HCols>
    </HBase>
    <HConv>
      <hTitle />
      <HCols Market="UK" MarketLabel="UK" Language="en-GB">
        <HCol>
          <HColId>1</HColId>
          <HValue>newborn</HValue>
        </HCol>
        <HCol>
          <HColId>2</HColId>
          <HValue>0-3m</HValue>
        </HCol>
        <HCol>
          <HColId>3</HColId>
          <HValue>3-6m</HValue>
        </HCol>
        <HCol>
          <HColId>4</HColId>
          <HValue>6-12m</HValue>
        </HCol>
        <HCol>
          <HColId>5</HColId>
          <HValue>12-18m</HValue>
        </HCol>
        <HCol>
          <HColId>6</HColId>
          <HValue>18-24m</HValue>
        </HCol>
        <HCol>
          <HColId>7</HColId>
          <HValue>2-3y</HValue>
        </HCol>
      </HCols>
      <HCols Market="US" MarketLabel="US" Language="en-US">
        <HCol>
          <HColId>1</HColId>
          <HValue>newborn</HValue>
        </HCol>
        <HCol>
          <HColId>2</HColId>
          <HValue>0-3m</HValue>
        </HCol>
        <HCol>
          <HColId>3</HColId>
          <HValue>3-6m</HValue>
        </HCol>
        <HCol>
          <HColId>4</HColId>
          <HValue>6-12m</HValue>
        </HCol>
        <HCol>
          <HColId>5</HColId>
          <HValue>12-18m</HValue>
        </HCol>
        <HCol>
          <HColId>6</HColId>
          <HValue>18-24m</HValue>
        </HCol>
        <HCol>
          <HColId>7</HColId>
          <HValue>2-3y</HValue>
        </HCol>
      </HCols>
      <HCols Market="DE" MarketLabel="Deutsch" Language="de-DE">
        <HCol>
          <HColId>1</HColId>
          <HValue>newborn</HValue>
        </HCol>
        <HCol>
          <HColId>2</HColId>
          <HValue>0-3M</HValue>
        </HCol>
        <HCol>
          <HColId>3</HColId>
          <HValue>3-6M</HValue>
        </HCol>
        <HCol>
          <HColId>4</HColId>
          <HValue>6-12M</HValue>
        </HCol>
        <HCol>
          <HColId>5</HColId>
          <HValue>12-18M</HValue>
        </HCol>
        <HCol>
          <HColId>6</HColId>
          <HValue>18-24M</HValue>
        </HCol>
        <HCol>
          <HColId>7</HColId>
          <HValue>2-3J</HValue>
        </HCol>
      </HCols>
      <HCols Market="AT" MarketLabel="AT" Language="de-AT">
        <HCol>
          <HColId>1</HColId>
          <HValue>newborn</HValue>
        </HCol>
        <HCol>
          <HColId>2</HColId>
          <HValue>0-3M</HValue>
        </HCol>
        <HCol>
          <HColId>3</HColId>
          <HValue>3-6M</HValue>
        </HCol>
        <HCol>
          <HColId>4</HColId>
          <HValue>6-12M</HValue>
        </HCol>
        <HCol>
          <HColId>5</HColId>
          <HValue>12-18M</HValue>
        </HCol>
        <HCol>
          <HColId>6</HColId>
          <HValue>18-24M</HValue>
        </HCol>
        <HCol>
          <HColId>7</HColId>
          <HValue>2-3J</HValue>
        </HCol>
      </HCols>
      <HCols Market="FR" MarketLabel="FR" Language="fr-FR">
        <HCol>
          <HColId>1</HColId>
          <HValue>newborn</HValue>
        </HCol>
        <HCol>
          <HColId>2</HColId>
          <HValue>0-3m</HValue>
        </HCol>
        <HCol>
          <HColId>3</HColId>
          <HValue>3-6m</HValue>
        </HCol>
        <HCol>
          <HColId>4</HColId>
          <HValue>6-12m</HValue>
        </HCol>
        <HCol>
          <HColId>5</HColId>
          <HValue>12-18m</HValue>
        </HCol>
        <HCol>
          <HColId>6</HColId>
          <HValue>18-24m</HValue>
        </HCol>
        <HCol>
          <HColId>7</HColId>
          <HValue>2-3a</HValue>
        </HCol>
      </HCols>
    </HConv>
    <HInfo />
  </Headers>
  <Values>
    <VRow>
      <VTitle>Height</VTitle>
      <VCols>
        <VCol>
          <VColId>1</VColId>
          <VData uom="cm">Up to 56</VData>
          <VData uom="inch">Up to 22</VData>
        </VCol>
        <VCol>
          <VColId>2</VColId>
          <VData uom="cm">56 - 60</VData>
          <VData uom="inch">22 - 23½</VData>
        </VCol>
        <VCol>
          <VColId>3</VColId>
          <VData uom="cm">60 - 68</VData>
          <VData uom="inch">23½ - 27</VData>
        </VCol>
        <VCol>
          <VColId>4</VColId>
          <VData uom="cm">68 - 76</VData>
          <VData uom="inch">27 - 30</VData>
        </VCol>
        <VCol>
          <VColId>5</VColId>
          <VData uom="cm">76 - 83</VData>
          <VData uom="inch">30 - 32½</VData>
        </VCol>
        <VCol>
          <VColId>6</VColId>
          <VData uom="cm">83 - 90</VData>
          <VData uom="inch">32½ - 35½</VData>
        </VCol>
        <VCol>
          <VColId>7</VColId>
          <VData uom="cm">90 - 98</VData>
          <VData uom="inch">35½ - 38½</VData>
        </VCol>
      </VCols>
    </VRow>
    <VRow>
      <VTitle>Weight</VTitle>
      <VCols>
        <VCol>
          <VColId>1</VColId>
          <VData uom="kg">Up to 4½</VData>
          <VData uom="lb">Up to 10</VData>
        </VCol>
        <VCol>
          <VColId>2</VColId>
          <VData uom="kg">4½ - 6</VData>
          <VData uom="lb">10 to 13</VData>
        </VCol>
        <VCol>
          <VColId>3</VColId>
          <VData uom="kg">6 - 8</VData>
          <VData uom="lb">13 - 17.5</VData>
        </VCol>
        <VCol>
          <VColId>4</VColId>
          <VData uom="kg">8 - 10</VData>
          <VData uom="lb">17.5 - 22</VData>
        </VCol>
        <VCol>
          <VColId>5</VColId>
          <VData uom="kg">10 - 12</VData>
          <VData uom="lb">22 - 26.5</VData>
        </VCol>
        <VCol>
          <VColId>6</VColId>
          <VData uom="kg">12 - 14</VData>
          <VData uom="lb">26.5 - 31</VData>
        </VCol>
        <VCol>
          <VColId>7</VColId>
          <VData uom="kg" />
          <VData uom="lb" />
        </VCol>
      </VCols>
    </VRow>
    <VRow>
      <VTitle>Chest</VTitle>
      <VCols>
        <VCol>
          <VColId>1</VColId>
          <VData uom="cm">Up to 38.5</VData>
          <VData uom="inch">Up to 15</VData>
        </VCol>
        <VCol>
          <VColId>2</VColId>
          <VData uom="cm">38.5 - 42</VData>
          <VData uom="inch">15 - 16½</VData>
        </VCol>
        <VCol>
          <VColId>3</VColId>
          <VData uom="cm">42 - 46</VData>
          <VData uom="inch">16½ - 18</VData>
        </VCol>
        <VCol>
          <VColId>4</VColId>
          <VData uom="cm">46 - 48</VData>
          <VData uom="inch">18 - 19</VData>
        </VCol>
        <VCol>
          <VColId>5</VColId>
          <VData uom="cm">48 - 50</VData>
          <VData uom="inch">19 - 19½</VData>
        </VCol>
        <VCol>
          <VColId>6</VColId>
          <VData uom="cm">50 - 52</VData>
          <VData uom="inch">19½ - 20½</VData>
        </VCol>
        <VCol>
          <VColId>7</VColId>
          <VData uom="cm">52 - 54</VData>
          <VData uom="inch">20½ - 21½</VData>
        </VCol>
      </VCols>
    </VRow>
    <VRow>
      <VTitle>Waist</VTitle>
      <VCols>
        <VCol>
          <VColId>1</VColId>
          <VData uom="cm">Up to 37</VData>
          <VData uom="inch">Up to 14½</VData>
        </VCol>
        <VCol>
          <VColId>2</VColId>
          <VData uom="cm">37 - 39</VData>
          <VData uom="inch">14½ - 15½</VData>
        </VCol>
        <VCol>
          <VColId>3</VColId>
          <VData uom="cm">39 - 43</VData>
          <VData uom="inch">15½ - 17</VData>
        </VCol>
        <VCol>
          <VColId>4</VColId>
          <VData uom="cm">43 - 44</VData>
          <VData uom="inch">17 - 17½</VData>
        </VCol>
        <VCol>
          <VColId>5</VColId>
          <VData uom="cm">44 - 46</VData>
          <VData uom="inch">17½ - 18</VData>
        </VCol>
        <VCol>
          <VColId>6</VColId>
          <VData uom="cm">46 - 48</VData>
          <VData uom="inch">18 - 19</VData>
        </VCol>
        <VCol>
          <VColId>7</VColId>
          <VData uom="cm">48 - 50</VData>
          <VData uom="inch">19 - 19½</VData>
        </VCol>
      </VCols>
    </VRow>
  </Values>
  <Footer />
</SizeGuide>'


SELECT @X

How can I produce the picture below from the xml above?

the field names are static.
but how would I query them?

or at least if I had a starting point…

enter image description here

so far I am here:

       --Columns

   SELECT
      Column1 = XCol.value('(VTitle)[1]', 'varchar(20)')
       -- ,Column2 = XCol.value('VTitle[1]/..', 'varchar(20)')
   FROM 
      @X.nodes('/SizeGuide/Values/VRow[1]') AS XTbl(XCol)

       UNION ALL

       SELECT
      Column1 = XCol.value('(VTitle)[1]', 'varchar(20)')
       --  ,Column2 = XCol.value('VTitle[1]/..', 'varchar(20)')
   FROM 
      @X.nodes('/SizeGuide/Values/VRow[2]') AS XTbl(XCol)

       --Rows

   SELECT
      Row1 = XCol.value('(VData)[1]', 'varchar(20)')
         --,Column2 = XCol.value('(VTitle)[1]/(VCols)[1]/(VCol)[1]', 'varchar(20)')
   FROM 
      @X.nodes('/SizeGuide/Values/VRow/VCols/VCol') AS XTbl(XCol)

and this is what I get:
enter image description here

Best Answer

This gives the output you need:

SELECT x.y.value('@Market', 'varchar(100)') as col0
    , x.y.value('HCol[1]/HValue[1]', 'varchar(100)') as col1
    , x.y.value('HCol[2]/HValue[1]', 'varchar(100)') as col2
    , x.y.value('HCol[3]/HValue[1]', 'varchar(100)') as col3
    , x.y.value('HCol[4]/HValue[1]', 'varchar(100)') as col4
    , x.y.value('HCol[5]/HValue[1]', 'varchar(100)') as col5
    , x.y.value('HCol[6]/HValue[1]', 'varchar(100)') as col6
    , x.y.value('HCol[7]/HValue[1]', 'varchar(100)') as col7
From @X.nodes('SizeGuide/Headers/HConv/HCols')x(y)
Union All
SELECT x.y.value('VTitle[1]', 'varchar(100)') 
    ,  x.y.value('VCols[1]/VCol[1]/VData[1]', 'varchar(100)') 
    ,  x.y.value('VCols[1]/VCol[2]/VData[1]', 'varchar(100)') 
    ,  x.y.value('VCols[1]/VCol[3]/VData[1]', 'varchar(100)') 
    ,  x.y.value('VCols[1]/VCol[4]/VData[1]', 'varchar(100)') 
    ,  x.y.value('VCols[1]/VCol[5]/VData[1]', 'varchar(100)') 
    ,  x.y.value('VCols[1]/VCol[6]/VData[1]', 'varchar(100)') 
    ,  x.y.value('VCols[1]/VCol[7]/VData[1]', 'varchar(100)') 
From @X.nodes('SizeGuide/Values/VRow')x(y)

Output:

col0    | col1       | col2      | col3    | col4    | col5    | col6    | col7
UK      | newborn    | 0-3m      | 3-6m    | 6-12m   | 12-18m  | 18-24m  | 2-3y
US      | newborn    | 0-3m      | 3-6m    | 6-12m   | 12-18m  | 18-24m  | 2-3y
DE      | newborn    | 0-3M      | 3-6M    | 6-12M   | 12-18M  | 18-24M  | 2-3J
AT      | newborn    | 0-3M      | 3-6M    | 6-12M   | 12-18M  | 18-24M  | 2-3J
FR      | newborn    | 0-3m      | 3-6m    | 6-12m   | 12-18m  | 18-24m  | 2-3a
Height  | Up to 56   | 56 - 60   | 60 - 68 | 68 - 76 | 76 - 83 | 83 - 90 | 90 - 98
Weight  | Up to 4½   | 4½ - 6    | 6 - 8   | 8 - 10  | 10 - 12 | 12 - 14 | 
Chest   | Up to 38.5 | 38.5 - 42 | 42 - 46 | 46 - 48 | 48 - 50 | 50 - 52 | 52 - 54
Waist   | Up to 37   | 37 - 39   | 39 - 43 | 43 - 44 | 44 - 46 | 46 - 48 | 48 - 50
Related Question