FOR XML: How to prevent nesting elements

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

David Parenteau - 18 Jun 2009 14:55 GMT

Hi! I would like the following result returned, but I'm not able to find out
how...
Contact1 and COntact2 belong to the same Customer1. I would like the element
customer duplicated as below. How should I do that?

Note; Currently, I get always 2 contact under the same Customer elements.

SELECT Customer.ID, Contact.ContactID
FROM Customer JOIN Contact ON Customer.CustomerID = Contact.CustomerID
FOR XML AUTO, ELEMENTS

<Customer>
<CustomerID>Customer1</CustomerID>
<Contact>
<ContactID>Contact 1</ContactID>
</Contact>
</Customer>
<Customer>
<CustomerID>Customer1</CustomerID>
<Contact>
<ContactID>Contact 2</ContactID>
</Contact>
</Customer>

Thank you!

David Parenteau

Reply to this Message

Bob Beauchemin - 18 Jun 2009 19:47 GMT

How about (for SQL Server 2005 and later)

SELECT Customer.CustomerID AS [CustomerID],
Contact.ContractID AS [ContactID]
FROM Customer JOIN Contact ON Customer.CustomerID = Contact.CustomerID
FOR XML PATH('Customer')

> Hi! I would like the following result returned, but I'm not able to find
> out
[quoted text clipped - 25 lines]
>
> David Parenteau

Reply to this Message

David Parenteau - 18 Jun 2009 19:57 GMT

Sorry, I forgot to specify that it should be under SQL 2000, unfortunately.

> How about (for SQL Server 2005 and later)
>
[quoted text clipped - 32 lines]
> >
> > David Parenteau

Reply to this Message

Bob - 18 Jun 2009 23:18 GMT

I got this to work for SQL 2000 using FOR XML EXPLICIT:

SELECT
    1 AS Tag,
    NULL AS Parent,
    NULL AS [Customer!1],
    cu.ID AS [CustomerID!2],
    NULL AS [Contact!3],
    NULL AS [ContactID!4],
    co.ID AS [ID!5!hide]

FROM Customer cu
    INNER JOIN Contact co ON cu.CustomerID = co.CustomerID

UNION ALL

SELECT
    2 AS Tag,
    1 AS Parent,
    NULL,
    cu.ID,
    NULL,
    ContactID,
    co.ID AS [ID!5!hide]
FROM Customer cu
    INNER JOIN Contact co ON cu.CustomerID = co.CustomerID

UNION ALL

SELECT
    3 AS Tag,
    1 AS Parent,
    NULL,
    cu.ID,
    NULL,
    ContactID,
    co.ID AS [ID!5!hide]
FROM Customer cu
    INNER JOIN Contact co ON cu.CustomerID = co.CustomerID

UNION ALL

SELECT
    4 AS Tag,
    3 AS Parent,
    NULL,
    cu.ID,
    NULL,
    ContactID,
    co.ID AS [ID!5!hide]
FROM Customer cu
    INNER JOIN Contact co ON cu.CustomerID = co.CustomerID

ORDER BY [ID!5!hide], [CustomerID!2]

FOR XML EXPLICIT

> Hi! I would like the following result returned, but I'm not able to find out
> how...
[quoted text clipped - 24 lines]
>
> David Parenteau

Reply to this Message

David Parenteau - 19 Jun 2009 13:51 GMT

Hi Bob... I think I see, but if a customer has up to 10 contacts, should I
put up to 10 UNION ALL? Can the tag be the COntactID in order to get
"different" tag number but with only one UNION ALL?

David

> I got this to work for SQL 2000 using FOR XML EXPLICIT:
>
[quoted text clipped - 81 lines]
> >
> > David Parenteau

Reply to this Message

Bob - 19 Jun 2009 15:24 GMT

No that should work. Try it!

In FOR XML EXPLICIT, extra sections of UNION ALL are for extra columns, or
really elements and attributes in XML terms.

This type of thing is much easier to do in SQL 2005 onwards with FOR XML PATH.

HTH
wBob

> Hi Bob... I think I see, but if a customer has up to 10 contacts, should I
> put up to 10 UNION ALL? Can the tag be the COntactID in order to get
[quoted text clipped - 87 lines]
> > >
> > > David Parenteau

Reply to this Message

David Parenteau - 19 Jun 2009 15:40 GMT

I got the the BOL and it appears very difficult to understand this! I
understand that I can do what I want (more complex than the example). I will
pass time on this and let you know if I needmore help than the book online!

Merci bob :)

David Parenteau, Montreal.

> No that should work. Try it!
>
[quoted text clipped - 97 lines]
> > > >
> > > > David Parenteau

Reply to this Message

Bob - 19 Jun 2009 17:09 GMT

Post over on the MSDN newsgroup which is busier than this one and someone is
more likely to pick it up:

http://social.msdn.microsoft.com/forums/en-US/sqlxml/threads/

> I got the the BOL and it appears very difficult to understand this! I
> understand that I can do what I want (more complex than the example). I will
[quoted text clipped - 105 lines]
> > > > >
> > > > > David Parenteau

Reply to this Message

Krish - 22 Jun 2009 22:30 GMT

David,

Take a look at the following utility:
http://thisisnotcnn.blogspot.com/2009/06/exportimport-relational-data-across-sql.html

Krish

> Hi! I would like the following result returned, but I'm not able to find
> out
[quoted text clipped - 25 lines]
>
> David Parenteau

Reply to this Message