CODEWITHSUNDEEP
sqlsql-serverxmlt-sqlstored-procedures
binit joshi
binit joshi

Reputation: 137

XML from Table in TSQL with varying attribute values

I need an XML from the following two tables provided below. The problem I am having is that I am not able to separate out the attributes for each project in the XML.

declare @A table
(
  PROJECT_ID varchar(10), 
  SITE_ID varchar(10)
);

declare @B table
(
    PROJECT_ID varchar(10),
    ATTRIBUTE_NAME varchar(15)
)

insert into @A values
('PROJECT1', 'A'),
('PROJECT2', 'B');

insert into @B values
('PROJECT1', 'PERSONID'),
('PROJECT1', 'GIVENNAME'),
('PROJECT1', 'MIDDLENAME'),
('PROJECT1', 'FAMILYNAME'),
('PROJECT1', 'DATEOFBIRTH'),
('PROJECT1', 'SEX'),
('PROJECT2', 'PERSON_ID'),
('PROJECT2', 'GIVEN_NAME'),
('PROJECT2', 'MIDDLE_NAME'),
('PROJECT2', 'FAMILY_NAME'),
('PROJECT2', 'DATEOF_BIRTH');

The XML result I want is:

<SITEID>A</SITEID>
<Project>
    <Name>PROJECT 1</Name>
    <Columns>
        <PracticeColumn>
            <Name>PERSONID</Name>           
        </PracticeColumn>
        <PracticeColumn>
            <Name>GIVENNAME</Name>      
        </PracticeColumn>
        <PracticeColumn>
            <Name>MIDDLENAME</Name>     
        </PracticeColumn>
        <PracticeColumn>
            <Name>FAMILYNAME</Name>     
        </PracticeColumn>
        <PracticeColumn>
            <Name>DATEOFBIRTH</Name>        
        </PracticeColumn>
        <PracticeColumn>
            <Name>SEX</Name>        
        </PracticeColumn>       
    </Columns>
</Project>
<Project>
    <Name>PROJECT 2</Name>
    <Columns>
        <PracticeColumn>
            <Name>PERSON_ID</Name>          
        </PracticeColumn>
        <PracticeColumn>
            <Name>GIVEN_NAME</Name>     
        </PracticeColumn>
        <PracticeColumn>
            <Name>MIDDLE_NAME</Name>        
        </PracticeColumn>
        <PracticeColumn>
            <Name>FAMILY_NAME</Name>        
        </PracticeColumn>
        <PracticeColumn>
            <Name>DATEOF_BIRTH</Name>       
        </PracticeColumn>
    </Columns>
</Project>

Note: Each project can have a different set of attributes. So the XML has to cater to that.

This is what I have attempted:

SELECT a.PROJECT_ID as Name,                    
                (SELECT 
                    ATTRIBUTE_NAME as Name                      
                FROM @A a
                INNER JOIN @B b ON b.PROJECT_ID = a.PROJECT_ID                  
                --WHERE  a.SITE_ID = 'A'                        
                FOR XML PATH ('PracticeColumn'), Root('Columns'),TYPE 
                )
        FROM @A a
        --WHERE  .SITE_ID = 'A'
        FOR XML PATH ('Project'),TYPE

And got the result below. Each Project in the xml has all attributes from all Projects.

<Project>
  <Name>PROJECT1</Name>
  <Columns>
    <PracticeColumn>
      <Name>PERSONID</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>GIVENNAME</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>MIDDLENAME</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>FAMILYNAME</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>DATEOFBIRTH</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>SEX</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>PERSON_ID</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>GIVEN_NAME</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>MIDDLE_NAME</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>FAMILY_NAME</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>DATEOF_BIRTH</Name>
    </PracticeColumn>
  </Columns>
</Project>
<Project>
  <Name>PROJECT2</Name>
  <Columns>
    <PracticeColumn>
      <Name>PERSONID</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>GIVENNAME</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>MIDDLENAME</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>FAMILYNAME</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>DATEOFBIRTH</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>SEX</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>PERSON_ID</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>GIVEN_NAME</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>MIDDLE_NAME</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>FAMILY_NAME</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>DATEOF_BIRTH</Name>
    </PracticeColumn>
  </Columns>
</Project>

Upvotes: 0

Views: 45

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

You need a correlated sub-query like here. You do not JOIN them, but use an appropriate WHERE-clause:

declare @A table
(
  PROJECT_ID varchar(10), 
  SITE_ID varchar(10)
);

declare @B table
(
    PROJECT_ID varchar(10),
    ATTRIBUTE_NAME varchar(15)
)

insert into @A values
('PROJECT1', 'A'),
('PROJECT2', 'B');

insert into @B values
('PROJECT1', 'PERSONID'),
('PROJECT1', 'GIVENNAME'),
('PROJECT1', 'MIDDLENAME'),
('PROJECT1', 'FAMILYNAME'),
('PROJECT1', 'DATEOFBIRTH'),
('PROJECT1', 'SEX'),
('PROJECT2', 'PERSON_ID'),
('PROJECT2', 'GIVEN_NAME'),
('PROJECT2', 'MIDDLE_NAME'),
('PROJECT2', 'FAMILY_NAME'),
('PROJECT2', 'DATEOF_BIRTH');

--The query

SELECT SITE_ID AS SITEID
     ,(
       SELECT a2.PROJECT_ID AS [Name]
             ,(
                SELECT b.ATTRIBUTE_NAME AS [PracticeColumn/Name]
                FROM @B b 
                WHERE b.PROJECT_ID=a2.PROJECT_ID
                FOR XML PATH(''),TYPE
              ) AS [Columns]
       FROM @A a2 
       WHERE a2.SITE_ID=a.SITE_ID
       FOR XML PATH('Project'),TYPE
      )
FROM @A a
GROUP BY SITE_ID
FOR XML PATH('');

The result

<SITEID>A</SITEID>
<Project>
  <Name>PROJECT1</Name>
  <Columns>
    <PracticeColumn>
      <Name>PERSONID</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>GIVENNAME</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>MIDDLENAME</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>FAMILYNAME</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>DATEOFBIRTH</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>SEX</Name>
    </PracticeColumn>
  </Columns>
</Project>
<SITEID>B</SITEID>
<Project>
  <Name>PROJECT2</Name>
  <Columns>
    <PracticeColumn>
      <Name>PERSON_ID</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>GIVEN_NAME</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>MIDDLE_NAME</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>FAMILY_NAME</Name>
    </PracticeColumn>
    <PracticeColumn>
      <Name>DATEOF_BIRTH</Name>
    </PracticeColumn>
  </Columns>
</Project>

Some remarks

I did not know how you want to include the <SITEID> into your XML. The expected result is not complete in this point of view. So I use three levels of correlated sub-queries. but I think the <SITEID> was better included into the <Project>-element or might be an Xml-attribute like here <Project SITEID="A">. You might want to group all projects of a site within the <SITEID> too. This is up to you.

Upvotes: 1

Related Questions