parent nodes for xml explicit

Question

Post reply

parent nodes for xml explicit

danielah

SSC Veteran

Points: 271
More actions
September 13, 2010 at 4:33 pm

#103983

Hi there-
I am trying to shape an xml result from a query using xml explicit. I am very new to using for xml explicit queries. The query retrieves transaction data from a t-log table and lists different line items based on what the line item type is. I got most of the nodes correct for the xml transaction/customer file based on a certain schema, but the detailed line items portion is giving me a little trouble. Here is an example of what i am trying to get as a result.
<Transaction>
<LineItem>
<Sale>
<ItemSKU>6099</ItemSKU>
<Quantity>1.0000</Quantity>
<ExtendedAmount>19.1100</ExtendedAmount>
</Sale>
</LineItem>
<LineItem>
<Sale>
<ItemSKU>743715004184</ItemSKU>
<Quantity>1.0000</Quantity>
<ExtendedAmount>18.9900</ExtendedAmount>
</Sale>
</LineItem>
</Transaction>
But instead i get:
<Transaction>
<LineItem>
<Sale>
<ItemSKU>6099</ItemSKU>
<Quantity>1.0000</Quantity>
<ExtendedAmount>19.1100</ExtendedAmount>
</Sale>
<Sale>
<ItemSKU>743715004184</ItemSKU>
<Quantity>1.0000</Quantity>
<ExtendedAmount>18.9900</ExtendedAmount>
</Sale>
</LineItem>
</Transaction>
This is the sql that i am using.
select 1 as Tag,
null as Parent,
null as [Transaction!1!LineItem!element],
null as [LineItem!2!Sale!element],
null as [Sale!3!ItemSKU!element],
null as [Sale!3!Quantity!element],
null as [Sale!3!ExtendedAmount!element]
from openquery(TLog_hqtj,
'select
trn_receiptnumber as TransactionID,
tli_scancode as Sale_ItemSKU,
tli_quantity as Sale_Quantity,
extendedprice as Sale_ExtendedAmount
from v_TJTrans
') [Transaction]
union
select 2 as Tag,
1 as Parent,
null as [Transaction!1!LineItem!element],
null as [LineItem!2!Sale!element],
null as [Sale!3!ItemSKU!element],
null as [Sale!3!Quantity!element],
null as [Sale!3!ExtendedAmount!element]
from openquery(TLog_hqtj,
'select
trn_receiptnumber as TransactionID,
tli_scancode as Sale_ItemSKU,
tli_quantity as Sale_Quantity,
extendedprice as Sale_ExtendedAmount
from v_TJTrans
') [Transaction]
union
select 3 as Tag,
2 as Parent,
null as [Transaction!1!LineItem!element],
null as [LineItem!2!Sale!element],
Sale_ItemSKU as [Sale!3!ItemSKU!element],
Sale_Quantity as [Sale!3!Quantity!element],
Sale_ExtendedAmount as [Sale!3!ExtendedAmount!element]
from openquery(TLog_hqtj,
'select
trn_receiptnumber as TransactionID,
tli_scancode as Sale_ItemSKU,
tli_quantity as Sale_Quantity,
extendedprice as Sale_ExtendedAmount
from v_TJTrans
') [Transaction]
for xml explicit
Does anyone know any tricks on how to shape the nodes correctly?
Thank you in advace!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Toby Harman SSCarpal Tunnel Points: 4168 More actions · Answer 1

Try taking a look at the PATH and ROOT options in the FOR XML clause.

You should be able to do this in a single select statement rather than 3 selects UNIONed

select

trn_receiptnumber as TransactionID,

tli_scancode as Sale_ItemSKU,

tli_quantity as Sale_Quantity,

extendedprice as Sale_ExtendedAmount

from v_TJTrans

FOR XML

PATH ('LineItem')

, ROOT ('Transaction')

danielah SSC Veteran Points: 271 More actions · Answer 2

Thank you for your reply!

I am not very familiar with for xml path and couldn't get it to work. Even tried a simple select statement and it keeps telling me 'incorrect syntax near 'path''. Also, the reason i chose xml explicit is because the complete xml output is more complex and the xml i put here was just a part of it where i can't figure out the 'LineItem' node to wrap around each Item SKU. Can I do a child to parent node somehow in xml explicit for each data element that i pull from the database?

I will also include the complete xml file I am trying to get.

<Transaction>

<TransactionID>403241</TransactionID>

<RetailStoreID>001</RetailStoreID>

<SalesAssociateID>480186</SalesAssociateID>

<ReceiptDateTime>2010-08-20T14:12:14</ReceiptDateTime>

<Shopper>

<ShopperID>450005862</ShopperID>

<ShopperName>

<PersonFullName>

<FirstName>JANNA</FirstName>

<LastName>JONES</LastName>

</PersonFullName>

</ShopperName>

<ShopperAddress>

<AddressLine1>3048 WASHINGTON ST</AddressLine1>

<City>BOULDER</City>

<State>CO</State>

<PostalCode>80304</PostalCode>

</ShopperAddress>

<ShopperPhoneNumber>3034492291</ShopperPhoneNumber>

<ShopperEMail>OLEVNIKO@YAHOO.COM</ShopperEMail>

<LoyaltyProgramID>450008021</LoyaltyProgramID>

<LoyaltyMember>1</LoyaltyMember>

</Shopper>

<LineItem>

<Sale>

<ItemSKU>072440341906</ItemSKU>

<Quantity>1.0000</Quantity>

<ExtendedAmount>3.9900</ExtendedAmount>

</Sale>

</LineItem>

<LineItem>

<Sale>

<ItemSKU>092567102273</ItemSKU>

<Quantity>1.0000</Quantity>

<ExtendedAmount>3.9900</ExtendedAmount>

</Sale>

</LineItem>

<LineItem>

<Sale>

<ItemSKU>306960587132</ItemSKU>

<Quantity>1.0000</Quantity>

<ExtendedAmount>6.4900</ExtendedAmount>

</Sale>

</LineItem>

<LineItem>

<Tender TenderType = "Discount - 15% Moxie Moms">

<Amount>-1.0500</Amount>

</Tender>

</LineItem>

<LineItem>

<Tender TenderType = "Discount - 15% Moxie Moms">

<Amount>-0.9700</Amount>

</Tender>

</LineItem>

<LineItem>

<Tender TenderType = "Discount - 15% Moxie Moms">

<Amount>-0.8100</Amount>

</Tender>

</LineItem>

</Transaction>

Toby Harman SSCarpal Tunnel Points: 4168 More actions · Answer 3

Hmm - According to the documentation here http://msdn.microsoft.com/en-us/library/ms345137(SQL.90).aspx that should have worked.

Just checking. Which version of SQL are you using?

danielah SSC Veteran Points: 271 More actions · Answer 4

I'll check it out. The sql instance i am using is SQL Server 2005 9.00

Thanks!

danielah SSC Veteran Points: 271 More actions · Answer 5

Ops actually it's SQL Server 2000. I am using SQL 2005 Management Studio Express as my query analyzer. I don't think sql server 2000 supports xml path. Xml explicit would be my only option.