CODEWITHSUNDEEP
xmlt-sql
salvationishere
salvationishere

Reputation: 3511

XML: Same parent

How could I design an XML query to return all children with the same parent under the same Path?
e.g. 

create table parent_child
(
ParentID varchar(50),
ChildID varchar(50)
)
insert parent_child
values ('Parent1','Child1'),('Parent1','Child2'),('Parent1','Child3'),('Parent4','Child4'),('Parent5','Child5')
select * from parent_child

Using this code:

Select  
ParentID ,
ChildID

FROM parent_child
FOR XML PATH('Parent'), type, root('Provider')

This is displaying as:

<Provider>
  <Parent>
    <ParentID>Parent1</ParentID>
    <ChildID>Child1</ChildID>
  </Parent>
  <Parent>
    <ParentID>Parent1</ParentID>
    <ChildID>Child2</ChildID>
  </Parent>
  <Parent>
    <ParentID>Parent1</ParentID>
    <ChildID>Child3</ChildID>
  </Parent>
  <Parent>
    <ParentID>Parent4</ParentID>
    <ChildID>Child4</ChildID>
  </Parent>
  <Parent>
    <ParentID>Parent5</ParentID>
    <ChildID>Child5</ChildID>
  </Parent>
</Provider>

When instead, I want this to display as:

<Provider>
  <Parent>
    <ParentID>Parent1</ParentID>
    <ChildID>Child1</ChildID>
    <ChildID>Child2</ChildID>
    <ChildID>Child3</ChildID>
  </Parent>
  <Parent>
    <ParentID>Parent4</ParentID>
    <ChildID>Child4</ChildID>
  </Parent>
  <Parent>
    <ParentID>Parent5</ParentID>
    <ChildID>Child5</ChildID>
  </Parent>
</Provider>

How do I design this query?

Upvotes: 0

Views: 30

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239754

This seems to work. I've switched your sample data to a table variable, just to make the script self contained and repeatable without leaving any artifacts behind:

declare @parent_child table
(
ParentID varchar(50),
ChildID varchar(50)
)
insert @parent_child
values ('Parent1','Child1'),('Parent1','Child2'),('Parent1','Child3'),
       ('Parent4','Child4'),('Parent5','Child5')
select * from @parent_child

select
    ParentID,
    (select ChildID
     from @parent_child pc2
     where pc2.ParentID = pc1.ParentID for xml path(''),type)
from (select Distinct ParentID from @parent_child) pc1
for xml path('Parent'), type, root('Provider')

Result:

<Provider>
  <Parent>
    <ParentID>Parent1</ParentID>
    <ChildID>Child1</ChildID>
    <ChildID>Child2</ChildID>
    <ChildID>Child3</ChildID>
  </Parent>
  <Parent>
    <ParentID>Parent4</ParentID>
    <ChildID>Child4</ChildID>
  </Parent>
  <Parent>
    <ParentID>Parent5</ParentID>
    <ChildID>Child5</ChildID>
  </Parent>
</Provider>

The key part really is generating the Child elements in a correlated subquery, ensuring that they're not separately wrapped (PATH('')) and that the result is treated as already formed XML (TYPE) in the outer query.

Upvotes: 2

Related Questions