Post reply

Generate dynamic multilevel XML file from SQL Server table

  • June 12, 2016 at 5:53 am

    #400265

    Hi!

    I have a database with 5 tables. 3 tables are connect .

    I create one table where all data is merged. This table has a column GroupLevel that holds then level nr. Each level holds the subsequent lower level, e.g. group 1 holds group 2.

    The first of my tables contains products:

    ProductID ProductLanguageID ProductDefaultShopID ProductNumber ProductName ProductPrice

    100101@@SHOP1 LANG2 SHOP1 100101 AAA 159,61

    100102@@SHOP1 LANG2 SHOP1 100102 BBB 159,61

    100105@@SHOP1 LANG2 SHOP1 100105 CCC 159,61

    the second has groups:

    GroupID GroupLanguageID GroupName GroupNumber

    1@@SHOP1 LANG2 ABC 1

    1.01@@SHOP1 LANG2 BCD 1.01

    1.02@@SHOP1 LANG2 CDE 1.02

    1.03@@SHOP1 LANG2 DEF 1.03

    the third languages

    LanguageID LanguageCode2 LanguageName LanguageNativeName

    LANG1 En English English

    LANG2 De German Deutsch

    I have also 2 tables that don´t have any connections to another tables:

    Group Relations Table

    GroupRelationsGroupID GroupRelationsParentID

    1.01@@SHOP1 1@@SHOP1

    1.02@@SHOP1 1@@SHOP1

    1.02@@SHOP1 1@@SHOP1

    Group Product Relation Table

    GroupProductRelationGroupID GroupProductRelationProductID

    1.01@@SHOP1 576134@@SHOP1

    1.01@@SHOP1 327112@@SHOP1

    1.01@@SHOP1 457168@@SHOP1

    1.01@@SHOP1 457220@@SHOP1

    1.03@@SHOP1 457221@@SHOP1

    1.03@@SHOP1 163704@@SHOP1

    1.03@@SHOP1 163705@@SHOP1

    1.03@@SHOP1 163706@@SHOP1

    I created one table where all data is merged. This table has a column GroupLevel that holds a level nr. Each level holds the subsequent lower level, e.g. group 1 holds group 2, as in table below

    GroupLevel GroupID GroupLanguageID GroupName GroupNumber ParentGroupID GroupProductRelationGroupID GroupProductRelationProductID ProductID ProductLanguageID ProductNumber ProductName ProductPrice

    1 1@@SHOP1 LANG2 ABC 1 NULL NULL NULL NULL NULL NULL NULL NULL

    2 1.01@@SHOP1 LANG2 BCD 1.01 1@@SHOP1 1.01@@SHOP1 457163@@SHOP1 457163@@SHOP1 LANG2 457163 ProductName 299,81

    2 1.01@@SHOP1 LANG2 BCD 1.01 1@@SHOP1 1.01@@SHOP1 457162@@SHOP1 457162@@SHOP1 LANG2 457162 ProductName 163,14

    2 1.03@@SHOP1 LANG2 DEF 1.03 1@@SHOP1 1.03@@SHOP1 159151@@SHOP1 159151@@SHOP1 LANG2 159151 ProductName 10

    2 1.03@@SHOP1 LANG2 DEF 1.03 1@@SHOP1 1.03@@SHOP1 159150@@SHOP1 159150@@SHOP1 LANG2 159150 ProductName 10

    2 1.03@@SHOP1 LANG2 DEF 1.02 1@@SHOP1 1.03@@SHOP1 159122@@SHOP1 159122@@SHOP1 LANG2 159122 ProductName 309,35

    3 1.13@@SHOP1 LANG2 GGG 1.13 1.03@@SHOP1 1.13@@SHOP1 3459652@@SHOP1 3459652@@SHOP1LANG2 3459652 ProductName 309,35

    4 1.14@@SHOP1 LANG2 BBB 1.14 1.13@@SHOP1 1.13@@SHOP1 564326@@SHOP1 564326@@SHOP1 LANG2 564326 ProductName 309,35

    My question is how to create a dynamical XML file with max 3 levels, where all items on levels after the third one are merged to(included into) the 3rd level. I also need that each item of these levels (the ones merged to the third) has an attribute that says/shows which level they originally belonged to. So for example if I had 5 levels and level 4 and 5 where merged into level 3, all items from level 4 should have a tag saying “previously in level 4” and from level 5 "previously in level 5". This should work regardless of how many levels there are.

    I created code but it doesn't work as needed

    SELECT lvl1.GroupName AS groupName, lvl1.GroupNumber

    ,(

    SELECT prod1.ProductName AS itemName

    ,prod1.ProductNumber AS itemNumber,

    prod1.ProductPrice AS itemPrice,

    prod1.GroupID,

    prod1.GroupName

    FROM #tmpFullResult AS prod1

    WHERE prod1.GroupLevel=1

    AND prod1.GroupID=lvl1.GroupID

    FOR XML PATH('product'),ROOT('products'),TYPE

    )

    ,(

    SELECT lvl2.GroupName AS groupName, lvl2.GroupNumber

    ,(

    SELECT prod.ProductName AS '@id' ,prod.ProductNumber AS itemNumber

    ,prod.ProductPrice AS itemPrice

    FROM #tmpFullResult AS prod

    WHERE prod.ProductID IS NOT NULL

    AND prod.GroupLevel=2

    AND prod.ProductID=lvl2.ProductID

    AND prod.GroupID=lvl2.GroupID

    FOR XML PATH('product'),ROOT('products'),TYPE

    )

    FROM #tmpFullResult AS lvl2

    WHERE lvl2.GroupLevel=2

    AND lvl2.ParentGroupID=lvl1.GroupID

    group by lvl2.GroupName , lvl2.GroupNumber,lvl2.GroupID, lvl2.ProductID

    FOR XML PATH('productGroup2'),TYPE

    )

    FROM #tmpFullResult AS lvl1

    WHERE lvl1.ParentGroupID IS NULL

    group by lvl1.GroupName , lvl1.GroupNumber,lvl1.GroupID, lvl1.ProductID

    FOR XML PATH('productgroup1'),ROOT('root')

    GO

  • June 13, 2016 at 4:50 pm

    #1884794

    Since I'm interested in an answer to this, I'll "bump" the post for you.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 13, 2016 at 8:35 pm

    #1884806

    I'm going to see if I can do this. In the meantime, if anyone else wants to try I've created some more usable sample data.

    SET NOCOUNT ON;

    IF OBJECT_ID('tempdb..#Products')

    IS NOT NULL DROP TABLE #Products;

    IF OBJECT_ID('tempdb..#Groups')

    IS NOT NULL DROP TABLE #Groups;

    IF OBJECT_ID('tempdb..#Languages')

    IS NOT NULL DROP TABLE #Languages;

    IF OBJECT_ID('tempdb..#GroupRelations')

    IS NOT NULL DROP TABLE #GroupRelations;

    IF OBJECT_ID('tempdb..#GroupProductRelation')

    IS NOT NULL DROP TABLE #GroupProductRelation;

    SELECT * INTO #Products FROM (VALUES

    ('100101@@SHOP1','LANG2','SHOP1',100101,'AAA','159,61'),

    ('100102@@SHOP1','LANG2','SHOP1',100102,'BBB','159,61'),

    ('100105@@SHOP1','LANG2','SHOP1',100105,'CCC','159,61')) Products

    (

    ProductID,

    ProductLanguageID,

    ProductDefaultShopID,

    ProductNumber,

    ProductName,

    ProductPrice

    );

    SELECT * INTO #Groups FROM (VALUES

    ('1@@SHOP1','LANG2','ABC',1),

    ('1.01@@SHOP1','LANG2','BCD',1.01),

    ('1.02@@SHOP1','LANG2','CDE',1.02),

    ('1.03@@SHOP1','LANG2','DEF',1.03)) Groups

    (

    GroupID,

    GroupLanguageID,

    GroupName,

    GroupNumber

    );

    SELECT * INTO #Languages FROM (VALUES

    ('LANG1','En','English','English'),

    ('LANG2','De','German','Deutsch')) Languages

    (

    LanguageID,

    LanguageCode2,

    LanguageName,

    LanguageNativeName

    );

    SELECT * INTO #GroupRelations FROM (VALUES

    ('1.01@@SHOP1','1@@SHOP1'),

    ('1.02@@SHOP1','1@@SHOP1'),

    ('1.02@@SHOP1','1@@SHOP1')) GroupRelations

    (

    GroupRelationsGroupID,

    GroupRelationsParentID

    );

    SELECT * INTO #GroupProductRelation FROM (VALUES

    ('1.01@@SHOP1','576134@@SHOP1'),

    ('1.01@@SHOP1','327112@@SHOP1'),

    ('1.01@@SHOP1','457168@@SHOP1'),

    ('1.01@@SHOP1','457220@@SHOP1'),

    ('1.03@@SHOP1','457221@@SHOP1'),

    ('1.03@@SHOP1','163704@@SHOP1'),

    ('1.03@@SHOP1','163705@@SHOP1'),

    ('1.03@@SHOP1','163706@@SHOP1')) GroupProductRelation

    (

    GroupProductRelationGroupID,

    GroupProductRelationProductID

    );

    [/code]

    Edit: used the wrong IFCode shortcut for my sample data.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

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

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