2

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

  • 1
    @JulienVavasseur its easy to find out using data explorer e.g. if you want to find out for a given user How many upvotes do I have for each tag?. Isn't it cool stuff ? – Kin Shah Sep 23 '15 at 16:21
  • The downvotes are for when a question is not clear, not useful or does not show neither work not effort. Is this really the case for this question?? I has been a great learning experience for me, thanks for the people who answered, both valid answers I don't even know which one to mark as "the answer" because both do the trick. – Marcello Miorelli Sep 23 '15 at 16:27
  • 1
    I upvoted it .. Its a legit question and shows your efforts as well. – Kin Shah Sep 23 '15 at 16:29
  • 1
    I just upvoted it too. What you added during the day shows that you were trying to solve it on your own and you were using the right stuffs. – Julien Vavasseur Sep 23 '15 at 18:54
2

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
2
declare @Results table (
    Header varchar(20)
    ,Column1 varchar(20)
    ,Column2 varchar(20)
    ,Column3 varchar(20)
    ,Column4 varchar(20)
    ,Column5 varchar(20)
    ,Column6 varchar(20)
    ,Column7 varchar(20)
    );

with Data
as (
    select Tab.Col.value('for $i in . return count(../*[. << $i]) + 1', 'int') as HeaderID
        ,Tab.Col.value('(@Market)[1]', 'VARCHAR(20)') as Header
        ,Tab1.Col1.value('HValue[1]', 'VARCHAR(20)') as ColumnData
    from @X.nodes('/SizeGuide/Headers/HConv/HCols') as Tab(Col)
    cross apply Tab.Col.nodes('HCol') as Tab1(Col1)
    )
insert into @Results
select Header
    ,[1] as Column1
    ,[2] as Column2
    ,[3] as Column3
    ,[4] as Column4
    ,[5] as Column5
    ,[6] as Column6
    ,[7] as Column7
from (
    select HeaderID
        ,Header
        ,ColumnData
        ,ROW_NUMBER() over (
            partition by Header order by Header
            ) as [RowNum]
    from Data
    ) a
PIVOT(MAX(ColumnData) for RowNum in (
            [1]
            ,[2]
            ,[3]
            ,[4]
            ,[5]
            ,[6]
            ,[7]
            )) as pvt
order by HeaderID;

with Data
as (
    select Tab.Col.value('for $i in . return count(../*[. << $i]) + 1', 'int') as HeaderID
        ,Tab.Col.value('VTitle[1]', 'VARCHAR(20)') as Header
        ,Tab1.Col1.value('VData[1]', 'VARCHAR(20)') as ColumnData
    from @X.nodes('/SizeGuide/Values/VRow') as Tab(Col)
    cross apply Tab.Col.nodes('VCols/VCol') as Tab1(Col1)
    )
insert into @Results
select Header
    ,[1] as Column1
    ,[2] as Column2
    ,[3] as Column3
    ,[4] as Column4
    ,[5] as Column5
    ,[6] as Column6
    ,[7] as Column7
from (
    select HeaderID
        ,Header
        ,ColumnData
        ,ROW_NUMBER() over (
            partition by Header order by Header
            ) as [RowNum]
    from Data
    ) a
PIVOT(MAX(ColumnData) for RowNum in (
            [1]
            ,[2]
            ,[3]
            ,[4]
            ,[5]
            ,[6]
            ,[7]
            )) as pvt
order by HeaderID

select *
from @Results

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.