Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQLXML scrambled output

Status
Not open for further replies.
mawdryn

mawdryn

Technical User
Oct 14, 2002
26
AU
Hi all,

My apologies if this is a bit long winded...

I have an issue here that I hope someone can help with. I'm *very* new at XML... this is my third day on it :p

successfully created a SQLXML template file ok that reads information from a sql2000 database and outputs to IIS5 as long as only one contact is found for a particular company

xml output (with one record match) as follows:
===========================================================
- <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
- <customer accountno="11231" company="Company1">
- <contact Name="Foo Bar">
<Phone_Office>12345678</Phone_Office>
<Address1>27 Some Place</Address1>
<City>NEWCASTLE</City>
<State>NSW</State>
<Postcode>2300</Postcode>
</contact>
- <support>
<SuppMaint>None</SuppMaint>
<Product>Product1</Product>
<Version>1.7</Version>
</support>
</customer>
</ROOT>
===========================================================
Problem is that if more than one contact is found for that company, the entries are all messed up. output shown below:
===========================================================
- <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
- <customer accountno="11234" company="Company2">
- <contact Name="Person 1">
<Phone_Office>12345678</Phone_Office>
<Phone_Direct>87654321</Phone_Direct>
<Phone_Mobile>23456789</Phone_Mobile>
<Address1>1 another place</Address1>
<City>SYDNET</City>
<State>NSW</State>
<Postcode>2000</Postcode>
</contact>
- <support>
<SuppMaint>12 Months</SuppMaint>
<Product>Product2</Product>
<Version>2.2</Version>
</support>
- <support>
<SuppMaint>12 Months</SuppMaint>
<Product>Product2</Product>
<Version>2.2</Version>
</support>
- <contact Name="Person2">
<Phone_Office>12345678</Phone_Office>
<Phone_Direct>345998374/Phone_Direct>
<Phone_Mobile>004534573</Phone_Mobile>
<Address1>1 another place</Address1>
<City>SYDNEY</City>
<State>NSW</State>
<Postcode>2000</Postcode>
</contact>
</customer>
<customer accountno="11235" company="Company2" />
===========================================================
SQLXML template code is as follows:
===========================================================
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:header>
<sql:param name="company">COMPANY</sql:param>
</sql:header>
<sql:query>
SELECT 1 as Tag, NULL as Parent,
details.accountno as [customer!1!accountno],
details.company as [customer!1!company],
details.contact as [contact!2!Name],
NULL as [contact!2!Phone_Office!element],
NULL as [contact!2!Phone_Direct!element],
NULL as [contact!2!Phone_Mobile!element],
NULL as [contact!2!Address1!element],
NULL as [contact!2!Address2!element],
NULL as [contact!2!PO_Box!element],
NULL as [contact!2!City!element],
NULL as [contact!2!State!element],
NULL as [contact!2!Postcode!element],
NULL as [support!3!SuppMaint!element],
NULL as [support!3!Product!element],
NULL as [support!3!Version!element]

FROM contact1 details
WHERE details.company = @company

UNION ALL

SELECT 2,1,
details.accountno,
details.company,
details.contact,
details.phone1,
details.phone2,
details.phone3,
details.address1,
details.address2,
details.address3,
details.city,
details.state,
details.zip,
NULL,
NULL,
NULL

FROM contact1 details
WHERE details.company = @company

UNION ALL

SELECT 3,1,
details.accountno,
details.company,
details.contact,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
support.usppmnttyp,
support.ucustprod,
support.uversion

FROM contact1 details, contact2 support
WHERE details.company = @company AND details.accountno = support.accountno

ORDER BY [customer!1!accountno]

For XML EXPLICIT
</sql:query>
</ROOT>
===========================================================
If anyone can tell me where I've gone wrong, or a better way to write this XML, please let me know. Thanks in advance.
 
Sort by date Sort by votes
check where the tags are being created - try this:

Code: 
 SELECT 1 as Tag, NULL as Parent,
      details.accountno as [customer!1!accountno],
      details.company    as [customer!1!company],
      Null        as [contact!2!Name],
      NULL        as [contact!2!Phone_Office!element],
      NULL        as [contact!2!Phone_Direct!element],
      NULL        as [contact!2!Phone_Mobile!element],
      NULL        as [contact!2!Address1!element],
      NULL        as [contact!2!Address2!element],
      NULL        as [contact!2!PO_Box!element],
      NULL        as [contact!2!City!element],
      NULL        as [contact!2!State!element],
      NULL        as [contact!2!Postcode!element],
      NULL        as [support!3!SuppMaint!element],
      NULL        as [support!3!Product!element],
      NULL        as [support!3!Version!element]

    FROM contact1 details
    WHERE details.company = @company
    
    UNION ALL

    SELECT 2,1,
      Null,
      Null,
      details.contact,
      details.phone1,
      details.phone2,
      details.phone3,
      details.address1,
      details.address2,
      details.address3,
      details.city,
      details.state,
      details.zip,
      NULL,
      NULL,
      NULL

    FROM contact1 details
    WHERE details.company = @company

    UNION ALL

    SELECT 3,1,
      Null,
      Null,
      Null,
      NULL,
      NULL,
      NULL,
      NULL,
      NULL,
      NULL,
      NULL,
      NULL,
      NULL,
      support.usppmnttyp,
      support.ucustprod,
      support.uversion

    FROM contact1 details, contact2 support
    WHERE details.company = @company AND details.accountno = support.accountno

    ORDER BY [customer!1!accountno]    

    For XML EXPLICIT

hope this helps

simon
 
Upvote 0 Downvote
Hi,

Thanks for the reply, however that doesn't work. I get the following error message:

<?MSSQLError HResult="0x80040e14" Source="Microsoft OLE DB Provider for SQL Server" Description="Parent tag ID 1 is not among the open tags. FOR XML EXPLICIT requires parent tags to be opened first. Check the ordering of the result set."?>
 
Upvote 0 Downvote
One thing which may be relevant is that the XML output is perfect until I enter the third (support) tier.
 
Upvote 0 Downvote
Fixed :)

seems I didn't include some extra Where clauses
Here's the code

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:header>
<sql:param name="company">COMPANY</sql:param>
</sql:header>
<sql:query>
SELECT 1 as Tag, NULL as Parent,
details.accountno as [customer!1!accountno],
details.company as [customer!1!company],
details.contact as [customer!1!contact],
NULL as [contact!2!Phone_Office!element],
NULL as [contact!2!Phone_Direct!element],
NULL as [contact!2!Phone_Mobile!element],
NULL as [contact!2!Address1!element],
NULL as [contact!2!Address2!element],
NULL as [contact!2!PO_Box!element],
NULL as [contact!2!City!element],
NULL as [contact!2!State!element],
NULL as [contact!2!Postcode!element],
NULL as [support!3!Support_Type!element],
NULL as [support!3!Product!element],
NULL as [support!3!Version!element]

FROM contact1 details
WHERE details.company = @company

UNION ALL

SELECT 2,1,
NULL,
NULL,
details.contact,
contact.phone1,
contact.phone2,
contact.phone3,
contact.address1,
contact.address2,
contact.address3,
contact.city,
contact.state,
contact.zip,
NULL,
NULL,
NULL

from contact1 contact, contact1 details
where details.company = @company and contact.contact = details.contact

UNION ALL

SELECT 3,1,
details.accountno,
NULL,
details.contact,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
support.usppmnttyp,
support.ucustprod,
support.uversion

from contact2 support, contact1 details
where details.company = @company and support.accountno = details.accountno

order by [customer!1!contact]

For XML EXPLICIT
</sql:query>
</ROOT>
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Glenn9999
  • Locked
  • Question
How to disable MSXML output escaping? 1
Replies
12
Views
384
atlopes
atlopes
momo2000
  • Locked
  • Question
How do I get the correct output in xslt?
Replies
2
Views
159
momo2000
momo2000
momo2000
  • Locked
  • Question
How do I get the right output using template? 2
Replies
12
Views
265
momo2000
momo2000
momo2000
  • Locked
  • Question
How do I output all charges with with Pending ID?
Replies
3
Views
105
k5tm
k5tm
momo2000
  • Locked
  • Question
How do I output un-deleted judgment? 1
Replies
2
Views
68
momo2000
momo2000

Part and Inventory Search

Sponsor

Back
Top