| kiss deez 2005-10-27, 9:26 am |
| Hello everyone,
I am having a couple problems with the FOR XML EXPLICIT clause.
Here's is my current layout (the minus sign represents the group
displayed in a browswer window).
<xml>
- <po>
<buyer>Mike</buyer>
<expectedcompanyname>White house</expectedcompanyname>
<expectedaddress1>123 Mainstreet Rd</expectedaddress1>
<expectedaddress2>Suite A</expectedaddress2>
<expectedaddress3/>
<expectedaddress4/>
<expectedcity>Cleveland</expectedcity>
<expectedstate>OH </expectedstate>
<expectedzip>44444</expectedzip>
<expectedattentionto>Susie</expectedattentionto>
<ponumber>00002977</ponumber>
<carrier/>
<carrieraccount/>
<transportterms/>
<dunnsnumber>1</dunnsnumber>
<hnote>Test comment was added here.</hnote>
<part>Shelf Bracket</part>
<quantity>10</quantity>
<price>9.90</price>
<pallvendor/>
<contract> </contract>
<date>06/18/05</date>
<notes/>
</po>
</xml>
Here's the select I am using:
------------------------------------------------------------------
SELECT 1 AS Tag, NULL AS Parent,
NULL AS [xml!1!xml],
NULL AS & #91;pallorder!2!buye
r!Element],
NULL AS & #91;pallorder!2!expe
ctedcompanyname!Elem
ent],
NULL AS & #91;pallorder!2!expe
ctedaddress1!Element
],
NULL AS & #91;pallorder!2!expe
ctedaddress2!Element
],
NULL AS & #91;pallorder!2!expe
ctedaddress3!Element
],
NULL AS & #91;pallorder!2!expe
ctedaddress4!Element
],
NULL AS & #91;pallorder!2!expe
ctedcity!Element],
NULL AS & #91;pallorder!2!expe
ctedstate!Element],
NULL AS & #91;pallorder!2!expe
ctedzip!Element],
NULL AS & #91;pallorder!2!expe
ctedattentionto!Elem
ent],
NULL AS & #91;pallorder!2!ponu
mber!Element],
NULL AS & #91;pallorder!2!carr
ier!Element],
NULL AS & #91;pallorder!2!carr
ieraccount!Element],
NULL AS & #91;pallorder!2!tran
sportterms!Element],
NULL AS & #91;pallorder!2!dunn
snumber!Element],
NULL AS & #91;pallorder!2!hnot
e!Element],
NULL AS & #91;line!3!part!Elem
ent],
NULL AS & #91;line!3!quantity!
Element],
NULL AS & #91;line!3!price!Ele
ment],
NULL AS & #91;line!3!pallvendo
r!Element],
NULL AS & #91;line!3!contract!
Element],
NULL AS & #91;line!3!date!Elem
ent],
NULL AS & #91;line!3!notes!Ele
ment]
UNION
SELECT 2, 1,
NULL,
po.Buyer,
po.CompanyName,
po.Address1,
po.Address2,
po.Address3,
po.Address4,
po.City,
po.State,
po.PostalCode,
po.AttentionTo,
po.PONumber,
po.Carrier,
po.CarrierAccount,
po.TransportTerms,
po.DunsId,
po.HeaderComment,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
FROM ##tmp_PO po
UNION
SELECT 3, 1,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
pol.Item,
pol.Quantity,
pol.Price,
pol.PallVendor,
pol.Contract,
pol.DateRequired,
pol.LineComment
FROM ##tmp_POLine pol
ORDER BY & #91;pallorder!2!ponu
mber!Element]
FOR XML EXPLICIT
The problem is that the same PO number will be repeatedly for each PO
line. If the PO has 3 lines, the po and all of it's data will be
displayed 3x in a row, with the item stuff being different.
I need all lines displayed on the appropriate PO number, each listed
under a sub element called <line>
Here's an example of the format I need:
<xml>
- <po>
<buyer>Mike</buyer>
<expectedcompanyname>Warehouse</expectedcompanyname>
<expectedaddress1>123 Mainstreet Rd</expectedaddress1>
<expectedaddress2>Suite A</expectedaddress2>
<expectedaddress3/>
<expectedaddress4/>
<expectedcity>Cleveland</expectedcity>
<expectedstate>OH </expectedstate>
<expectedzip>44444</expectedzip>
<expectedattentionto>Susie</expectedattentionto>
<ponumber>00002977</ponumber>
<carrier/>
<carrieraccount/>
<transportterms/>
<dunnsnumber>1</dunnsnumber>
<hnote>Test comment was added here.</hnote>
- <line>
<part>Shelf Bracket</part>
<quantity>10</quantity>
<price>9.90</price>
<pallvendor/>
<contract> </contract>
<date>06/18/05</date>
<notes/>
<line>
- <line>
<part>Part II</part>
<quantity>10</quantity>
<price>9.90</price>
<pallvendor/>
<contract> </contract>
<date>06/18/05</date>
<notes/>
<line>
- <line>
<part>Part IV</part>
<quantity>10</quantity>
<price>9.90</price>
<pallvendor/>
<contract> </contract>
<date>06/18/05</date>
<notes/>
<line>
</po>
</xml>
Lastly, is it possible to create this format without a root tag?
Meaning <xml> and </xml> are not displayed at all? I don't think this
is possible, but I thought I'd ask someone who might have a little more
exper. using xml w/ sql.
Thanks,
J
|