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

4
  • 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, 2015 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. Sep 23, 2015 at 16:27
  • 1
    I upvoted it .. Its a legit question and shows your efforts as well.
    – Kin Shah
    Sep 23, 2015 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. Sep 23, 2015 at 18:54

2 Answers 2

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 and acknowledge you have read our privacy policy.

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