generate XML from hierarchical table data

• hemanulvi

  SSC Enthusiast

  Points: 132

  More actions

  March 30, 2011 at 3:12 pm

  #104056

  Hi

  I have two tables one for the codes and other is the child mapping table to represent a parent child relation with the codes table.

  Table 1: codes

  Code ID | Code Value | code Group | code Desc

  1 Person 1 Accounts cashier

  2 Person 2 Finance teller

  3 Person 3 HR administrator

  4 Person 4 IT system admin

  Table 2: mapping

  Id (Primarykey) | code ID | Parent Code ID (both code ID and parent code ID are foreign keys with codes table 1 1 Null

  2 2

  1 3

  3 1

  4 4

  Null 5

  3 4

  I need the desired output as

  <Codes>

  <codeGroup codeGroup="Entity">

  <CodeParent codeID="18" codeDesc="Borrower" codeGroup="Entity">

  <ChildCodes codeGroup="Fin Reporting Type">

  <CodeParent codeID="20" codeDesc="Financial Statement - Annual" codeGroup="Fin Reporting Type" />

  <CodeParent codeID="21" codeDesc="Tax Returns" codeGroup="Fin Reporting Type" />

  </ChildCodes>

  <ChildCodes codeGroup="Covenant Type">

  <CodeParent codeID="24" codeDesc="Capital Expenditures" codeGroup="Covenant Type" />

  <CodeParent codeID="25" codeDesc="Cash Flow Coverage Ratio" codeGroup="Covenant Type" />

  </ChildCodes>

  </CodeParent>

  <CodeParent codeID="17" codeDesc="Guarantor" codeGroup="Entity">

  <ChildCodes codeGroup="Fin Reporting Type">

  <CodeParent codeID="20" codeDesc="Financial Statement - Annual" codeGroup="Fin Reporting Type" />

  <CodeParent codeID="22" codeDesc="Brokerage Statements" codeGroup="Fin Reporting Type" />

  </ChildCodes>

  <ChildCodes codeGroup="Covenant Type">

  <CodeParent codeID="27" codeDesc="Current Ratio" codeGroup="Covenant Type" />

  <CodeParent codeID="28" codeDesc="Debt Service Coverage Ratio" codeGroup="Covenant Type" />

  </ChildCodes>

  </CodeParent>

  </codeGroup>

  <codeGroup codeGroup="Entity Category">

  <CodeParent codeID="4" codeDesc="Company" codeGroup="Entity Category">

  <ChildCodes codeGroup="AFS Obligor Type">

  <CodeParent codeID="9" codeDesc="American Subsidiary or Agency of Japanese TR Co" codeGroup="AFS Obligor Type">

  <ChildCodes codeGroup="test type">

  <CodeParent codeID="29" codeDesc="xyz" codeGroup="test type" />

  <CodeParent codeID="30" codeDesc="www" codeGroup="test type" />

  </ChildCodes>

  </CodeParent>

  <CodeParent codeID="11" codeDesc="Cooperative" codeGroup="AFS Obligor Type" />

  <CodeParent codeID="13" codeDesc="Corporation" codeGroup="AFS Obligor Type" />

  </ChildCodes>

  </CodeParent>

  <CodeParent codeID="8" codeDesc="Individual" codeGroup="Entity Category">

  <ChildCodes codeGroup="AFS Obligor Type">

  <CodeParent codeID="1" codeDesc="US Bancorp Employee" codeGroup="AFS Obligor Type" />

  <CodeParent codeID="3" codeDesc="US Bancorp Officer" codeGroup="AFS Obligor Type" />

  </ChildCodes>

  </CodeParent>

  <CodeParent codeID="5" codeDesc="LLC" codeGroup="Entity Category" />

  <CodeParent codeID="6" codeDesc="Trust" codeGroup="Entity Category">

  <ChildCodes codeGroup="AFS Obligor Type">

  <CodeParent codeID="1" codeDesc="US Bancorp Employee" codeGroup="AFS Obligor Type" />

  </ChildCodes>

  </CodeParent>

  </codeGroup>

  <codeGroup codeGroup="Category">

  <CodeParent codeID="31" codeDesc="cat1" codeGroup="Category" />

  <CodeParent codeID="32" codeDesc="cat2" codeGroup="Category" />

  <CodeParent codeID="33" codeDesc="cat3" codeGroup="Category" />

  </codeGroup>

  <codeGroup codeGroup="Facility">

  <CodeParent codeID="34" codeDesc="cat1" codeGroup="Facility" >

  <ChildCodes codeGroup="BorrowerType">

  <CodeParent codeID="40" codeDesc="Employee" codeGroup="BorrowerType" />

  <CodeParent codeID="41" codeDesc="Officer" codeGroup="BorrowerType" />

  </ChildCodes>

  </CodeParent>

  <CodeParent codeID="35" codeDesc="cat2" codeGroup="Facility" />

  <CodeParent codeID="36" codeDesc="cat3" codeGroup="Facility" />

  </codeGroup>

  <codeGroup codeGroup="Loan">

  <CodeParent codeID="37" codeDesc="cat1" codeGroup="Loan" >

  <ChildCodes codeGroup="BorrowerType">

  <CodeParent codeID="40" codeDesc="Employee" codeGroup="BorrowerType" />

  <CodeParent codeID="41" codeDesc="Officer" codeGroup="BorrowerType" />

  </ChildCodes>

  </CodeParent>

  <CodeParent codeID="38" codeDesc="cat2" codeGroup="Loan" />

  <CodeParent codeID="39" codeDesc="cat3" codeGroup="Loan" />

  </codeGroup>

  </Codes>

  I am trying to use this function

  ALTER FUNCTION dbo.GetPartsSubTree(@PartNumberID int)

  RETURNS XML

  BEGIN RETURN

  (SELECT codeMappingTable.codeID AS "@codeID", codes.codeLiteral AS "@codeLiteral", codes.codeGroup AS "@codeGroup", dbo.GetPartsSubTree(codeMappingTable.codeID)

  FROM codeMappingTable,codes

  WHERE codeMappingTable.ParentcodeID=@PartNumberID and codes.codeID = codeMappingTable.codeID ORDER BY codeMappingTable.codeID

  FOR XML PATH('CodeParent'),ROOT('ChildCodes'),TYPE)

  END

  GO

  SELECT codeMappingTable.codeID AS "@codeID", codes.codeLiteral AS "@codeLiteral", codes.codeGroup AS "@codeGroup", dbo.GetPartsSubTree(codeMappingTable.codeID)

  FROM codeMappingTable,codes WHERE codeMappingTable.ParentcodeID is null and codes.codeID = codeMappingTable.codeID

  ORDER BY codeMappingTable.codeID

  FOR XML PATH('CodeParent'),ROOT('Codes'),TYPE

  However I am not able to get the solutions for siblings under the parent. I do get the parent child relations.

  Please help

• Russel Loski

  SSCertifiable

  Points: 7059

  More actions

  April 1, 2011 at 8:31 pm

  #1306747

  How many columns are in the second table? Some rows look to have three and some look to have two. Also, it looks like there is a null in the primary key column.

  Russel Loski, MCSE Business Intelligence, Data Platform