Generate dynamic multilevel XML file from SQL Server table

• Jeff Moden

  SSC Guru

  Points: 1004664

  More actions

  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)
  

• Alan Burstein

  SSC Guru

  Points: 61152

  More actions

  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