For XML question

  • TJ_T

    Old Hand

    Points: 321

    I am trying put some structure around my XML and running into an issue.  I would like <First>, <Second>, <Third> under the root <Start>
    Is it possible to create an empty namespace?  It seems Path is fixed at one

    SELECT 'ModelName' = (SELECT STRING_AGG(MNames, ', ')
    FROM manufacturers),

    'Vendor' = (SELECT STRING_AGG(VNames, ', ')
    FROM manufacturers)
    FOR XML PATH ('First') , ROOT ('Start')

     I have this:
    <Start>
    <First>
      <ModelName>Sterling</ModelName>
      <Vendor>Amco, ABC Corp.</Vendor>
    </First>
    </Start>

    I would like this <First> and <Second>:

    <Start>
             <First>
                       <ModelName>Sterling</ModelName>
                        <Vendor>Amco, ABC Corp.</Vendor>
              </First>
               <Second>
               </Second>

    </Start>

  • Mark Cowne

    One Orange Chip

    Points: 26685

    This will sort of give you what you want, but you have to be able to order your results and also what happens with more than 3 items. If you want <Fourth>,<Fifth>... then its very complex.
    with src as (
    select MNames,VNames,row_number() over(order by VNames) as rn
    from manufacturers
    )
    select
           case when rn = 1 then MNames end as "First/ModelName",
           case when rn = 1 then VNames end as "First/Vendor",
           case when rn = 2 then MNames end as "Second/ModelName",
           case when rn = 2 then VNames end as "Second/Vendor",
           case when rn = 3 then MNames end as "Third/ModelName",
           case when rn = 3 then VNames end as "Third/Vendor"
    from src
    order by VNames
    for xml path(''), root('Start');

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • TJ_T

    Old Hand

    Points: 321

    Wow!  Very nice. 🙂

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply