parent nodes for xml explicit

Question

Post reply

parent nodes for xml explicit

danielah

SSC Veteran

Points: 251
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: 4159 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: 251 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: 4159 More actions · Answer 3

Toby Harman

SSCarpal Tunnel

Points: 4159

September 15, 2010 at 4:22 pm

#1221768

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: 251 More actions · Answer 4

danielah

SSC Veteran

Points: 251

September 15, 2010 at 6:02 pm

#1221803

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

Thanks!

danielah SSC Veteran Points: 251 More actions · Answer 5

danielah

SSC Veteran

Points: 251

September 17, 2010 at 12:56 pm

#1223280

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.