CODEWITHSUNDEEP
xmlmappingschemabiztalk
Mohammad Zeni
Mohammad Zeni

Reputation: 13

Biztalk: Nested Flat File Schema

I have these two tables that I wish to create a nested XML schema out of

I want the schema to look something like:

<PERSON ID="1" FN="F1" LN="L1">
  <DETAIL ID="1" NUM="11"/>
  <DETAIL ID="1" NUM="12"/>
</PERSON>
<PERSON ID="2" FN="F2" LN="L2">
  <DETAIL ID="1" NUM="21"/>
  <DETAIL ID="1" NUM="22"/>
  <DETAIL ID="1" NUM="23"/>
</PERSON>

I call some SQL to get the data from the tables. One way I have tried is using an inner join on the "ID" field and the other way is using a stored procedure to return two sets of data, one for each table. The issue I am having in both ways is how do I map it to the above structure?

I end up getting one DETAIL record per PERSON record (PERSON repeats) or I get all DETAIL records per PERSON record (ID no longer matches).

Upvotes: 0

Views: 94

Answers (2)

Gruff
Gruff

Reputation: 555

Using FOR XML AUTO:

SELECT PERSON.ID, PERSON.FN, PERSON.LN, DETAIL.ID, DETAIL.NUM
FROM PERSON
JOIN DETAIL ON PERSON.ID = DETAIL.ID
FOR XML AUTO

Using FOR XML PATH:

SELECT p.ID '@ID', p.FN '@FN', p.LN '@LN', 
    (
        SELECT d.ID '@ID', d.NUM '@NUM'
        FROM DETAIL d
        WHERE p.ID = d.ID
        FOR XML PATH('DETAIL'), TYPE
    )
FROM PERSON p
FOR XML PATH('PERSON')

both will produce this: 

<PERSON ID="1" FN="F1" LN="L1">
  <DETAIL ID="1" NUM="11" />
  <DETAIL ID="1" NUM="12" />
</PERSON>
<PERSON ID="2" FN="F2" LN="L2">
  <DETAIL ID="2" NUM="21" />
  <DETAIL ID="2" NUM="22" />
  <DETAIL ID="2" NUM="23" />
</PERSON>

Upvotes: 1

Zee
Zee

Reputation: 840

If you try this sql:

SELECT
ID AS '@ID',
FN AS '@FN',
LN AS '@LN',
(
SELECT ID AS '@ID', NUM AS '@NUM' FROM Table_2 WHERE table_2.ID = dbo.Table_1.ID FOR XML PATH('DETAIL') ,TYPE 
) AS 'details'
FROM dbo.Table_1
FOR XML PATH('PERSON')

You will get some xml content like below:

<PERSON ID="1" FN="F1" LN="L1">
  <details>
    <DETAIL ID="1" NUM="11" />
    <DETAIL ID="1" NUM="12" />
  </details>
</PERSON>
<PERSON ID="2" FN="F2" LN="L2">
  <details>
    <DETAIL ID="2" NUM="21" />
    <DETAIL ID="2" NUM="22" />
    <DETAIL ID="2" NUM="23" />
  </details>
</PERSON>

Upvotes: 0

Related Questions