﻿<?xml version="1.0" encoding="utf-8"?><XmlProjectInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Id="cfcc9eca-8b9d-4290-95a0-61e5c0ba4d02" Name="Parent-child dimension" Type="0" OutputPath="build\" DefaultPublishProfile="Default" Version="2.12.3214.0" NotReplaceMacroInPreview="false" DisableMacroExtensions="false" TargitDbVersion="Version_1_281"><FileSystemObjects><IStorageInfo AssemblyQualifiedName="TARGIT.InMemory.ETL.Studio.Configuration.Xml.XmlProjectModelInfo"><XmlProjectModelInfo FileName="Parent-child dimension.impm" BuildAction="Yes" Id="343f1009-886e-43d5-aaaf-3928b840e30e" Created="2022-09-14T08:43:33.1348811+02:00" CreatedBy="TARGIT-INET\niels_t" Edited="2022-09-14T08:44:05.0667966+02:00" EditedBy="TARGIT-INET\niels_t" IsOpened="true" CubeName="Parent-child dimension" PublishToServer="true" Is32BitExecutionMode="false" IsBaseScriptLocked="false" ExecuteOnScheduleOnly="false" IsStandard="false" RetryAttempts="0" RetryIntervalMins="0" RestartOnServiceFailure="true" RemoveDatabaseFromScheduler="true"><Instructions><IStorageInfo AssemblyQualifiedName="TARGIT.InMemory.ETL.Studio.Configuration.Xml.Instructions.XmlScriptInstructionInfo"><XmlScriptInstructionInfo Visibility="0" IsLocked="false" ToolSurfaceViewModelClass="TARGIT.InMemory.ETL.Studio.ViewModels.ToolSurfaces.ScriptToolSurfaceViewModel" Name="Parent-child" Id="011fb7d4-a61d-4d95-a836-fb50522e98eb" Query="DATASOURCE [Sitecore Master] = SQLSERVER 'Persist Security Info=False;Data source=marksitecore;Initial Catalog=Update2_Sitecore_Master;Integrated Security=SSPI'&#xD;&#xA;&#xD;&#xA;IMPORT Items= [Sitecore Master].{SELECT Id, Name FROM Items} &#xD;&#xA;&#xD;&#xA;IMPORT [tmp_ItemHierarchy] = [Sitecore Master].{WITH MyTest as&#xD;&#xA;(&#xD;&#xA;  SELECT P.Id, P.ParentID, CAST(P.Id AS VarChar(Max)) as Level&#xD;&#xA;  FROM Items P&#xD;&#xA;  WHERE P.ParentID = '11111111-1111-1111-1111-111111111111'&#xD;&#xA;&#xD;&#xA;  UNION ALL&#xD;&#xA;&#xD;&#xA;  SELECT P1.Id, P1.ParentID, CAST(P1.Id AS VarChar(Max)) + ', ' + M.Level&#xD;&#xA;  FROM Items P1  &#xD;&#xA;  INNER JOIN MyTest M&#xD;&#xA;  ON M.Id = P1.ParentID&#xD;&#xA; )&#xD;&#xA; select * from myTest}&#xD;&#xA;declare @maxlevels as int &#xD;&#xA;&#xD;&#xA;set @maxlevels = {&#xD;&#xA;select max(level) as MaxLevel from &#xD;&#xA;(&#xD;&#xA;select len(level)-len(replace(level,',','')) level from tmp_ItemHierarchy order by len(level)-len(replace(level,',',''))  desc&#xD;&#xA;) a&#xD;&#xA;}&#xD;&#xA;&#xD;&#xA;DECLARE @i as int&#xD;&#xA;declare @levelcnt as int&#xD;&#xA;declare @leveltxt as string&#xD;&#xA;declare @newleveltxt as string&#xD;&#xA;declare @reallevel as int&#xD;&#xA;declare @levelname as string&#xD;&#xA;declare @begininsert as string&#xD;&#xA;declare @insertsql as string&#xD;&#xA;declare @createtablesql as string&#xD;&#xA;set @createtablesql=''&#xD;&#xA;&#xD;&#xA;FOR @i=0 TO @maxlevels&#xD;&#xA; set @createtablesql=@createtablesql+'[Level'+@i+'] string, [LevelName'+@i+'] string,'&#xD;&#xA;NEXT&#xD;&#xA;&#xD;&#xA;set @createtablesql='CREATE TABLE HierarchyTable ([Id] string,[ParentId] string,[Level] string,'+@createtablesql+' [dummy] string)'&#xD;&#xA;&#xD;&#xA;print @createtablesql&#xD;&#xA;&#xD;&#xA;scriptexec @createtablesql&#xD;&#xA;&#xD;&#xA;declare @feltdata as string&#xD;&#xA;&#xD;&#xA;&#xD;&#xA;WHILE tmp_ItemHierarchy.EOF=false&#xD;&#xA; &#xD;&#xA; set @feltdata=tmp_ItemHierarchy.Level+', '&#xD;&#xA; set @levelcnt = len(@feltdata)-len(replace(@feltdata,',',''))&#xD;&#xA; set @reallevel=@levelcnt-1&#xD;&#xA; set @insertsql=''&#xD;&#xA;  &#xD;&#xA;  for @i=0 to @levelcnt-1 &#xD;&#xA;  &#xD;&#xA;  set @leveltxt=trim(left(@feltdata,charindex(@feltdata,',')-1))   &#xD;&#xA;  set @feltdata=substring(@feltdata,charindex(@feltdata,',')+1)&#xD;&#xA;  set @levelname={select Name from items where id=trim(@leveltxt)}  &#xD;&#xA;&#xD;&#xA;  set @insertsql=@insertsql+''''+@leveltxt+''','''+@levelname+''','&#xD;&#xA;  set @reallevel=@reallevel-1&#xD;&#xA;    &#xD;&#xA;  next  &#xD;&#xA;  &#xD;&#xA;  set @begininsert=''&#xD;&#xA;  &#xD;&#xA;   set @reallevel=@levelcnt-1&#xD;&#xA;  for @i=0 to @levelcnt-1&#xD;&#xA;      set @begininsert=@begininsert+'[Level'+@reallevel+'],[LevelName'+@reallevel+'],'&#xD;&#xA;      &#xD;&#xA; set @reallevel=@reallevel-1&#xD;&#xA;  next&#xD;&#xA;  &#xD;&#xA;  set @begininsert=left(@begininsert,len(@begininsert)-1)&#xD;&#xA;  set @insertsql=left(@insertsql,len(@insertsql)-1) &#xD;&#xA; &#xD;&#xA; scriptexec 'INSERT INTO HierarchyTable (id,parentid,level,'+@begininsert+') VALUES ('''+tmp_itemHierarchy.Id+''','''+tmp_itemHierarchy.ParentId+''','''+tmp_itemHierarchy.Level+''','+@insertsql+')' &#xD;&#xA; &#xD;&#xA;movenext tmp_ItemHierarchy&#xD;&#xA;LOOP&#xD;&#xA;&#xD;&#xA;COLUMNIZE HierarchyTable&#xD;&#xA;&#xD;&#xA;IMPORT wdLandingPageHierarchyTable=me.{SELECT * FROM HierarchyTable}" IsDisableMacro="false" /></IStorageInfo></Instructions><Schedules><IStorageInfo AssemblyQualifiedName="TARGIT.InMemory.ETL.Studio.Configuration.Xml.XmlSchedule"><XmlSchedule Name="Nightly update" ScheduleValue="0 0 2 1/1 * ? *" ExecutionTimeLimit="0" /></IStorageInfo></Schedules></XmlProjectModelInfo></IStorageInfo></FileSystemObjects><PublishProfiles><IStorageInfo AssemblyQualifiedName="TARGIT.InMemory.ETL.Studio.Configuration.Xml.Publish.XmlPublishProfileInfo"><XmlPublishProfileInfo Name="Default"><PublishStrategyInfo xsi:type="XmlSchedulerServicePublishStrategyInfo" Server="localhost:9098" OpenOutputFolderInWindowsExplorer="false" ExecuteAndFetchResultsLocally="false" DeleteAllExistingFiles="false" /></XmlPublishProfileInfo></IStorageInfo></PublishProfiles><DataSources /><Macros /><Synchronization IsEnabled="false" /></XmlProjectInfo>