nest or loop sql output to xml

I have an SQL table that I'm exporting to xml.  I would like however to nest or loop the data.

These are the columns in the table:  DOC_NO, SCHEDULED_DATE, ORDER_DATE, OPS_NOTES, ITEM_CODE, NOTE, QTY_ORDERED, DELIV_METH,  
CUST_PO, SHIP_NAME, SHIP_ADDRESS, SHIP_CITY, SHIP_STATE, SHIP_ZIP, SHIP_COUNTRY, CONTACT_NAME, CONTACT_ADDRESS, CONTACT_CITY,
CONTACT_STATE, CONTACT_ZIP, CONTACT_COUNTRY, EXWORKS, SEQUENCE

I would like the xlm file to be organized so the file would be something like this:

Order 1
   Ship info
       ship name, address etc
   Contact info
       contact name, address etc
  Order details
      order date
      item 1
      item 2
      item 3
      exworks
      op_notes

Order 2
   Ship info
       ship name, address etc
   Contact info
       contact name, address etc
  Order details
      order date
      item 1
      item 2
      exworks
      op_notes

Order 3
   Ship info
       ship name, address etc
   Contact info
       contact name, address etc
  Order details
      order date
      item 1
      item 2
      item 3
      item 4
      exworks
      op_notes

Here is the code I'm using to export the table in case it is of any use:

SELECT @FILENAME = '\\IC08ST\ORDERS\AG_ORDERS.XML'

SELECT @SQLCMD = 'BCP ' +
                        '"SELECT * ' +
                        ' FROM EVEREST.DBO.A6_AG ' +
                        ' FOR XML PATH (''''), ROOT(''ORDERS''), TYPE "' +
                        ' QUERYOUT ' +
                        @FILENAME +
                        ' -c -T '

EXECUTE MASTER..XP_CMDSHELL @SQLCMD
-----

Is this possible when exporting from an SQL table?        

Thank in advance for your assistance.
rgolenorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh BhadauriaCommented:
Can you share your schema ... with some sample data..
It will help us to resolve the query.

Thanks,
Saurabh
0
PortletPaulfreelancerCommented:
yes please, and I have a particular question or two...

Is that information really in just one table?
or is that a temporary table produced by a stored procedure or query?

It may be far more efficient to use the normalized base tables.
e.g. your list above indicates a variable number of items per Order Detail, so I would have expected more than one table to be involved.

so if EVEREST.DBO.A6_AG is not the origin of the information, please provide the table definitions that are the origin.
0
rgolenorAuthor Commented:
Paul, that is a temporary table..  The data is accumulated from tables:  invoice, x_invoice and address.    Invoice contains the basic order information, x_invoice contains the line items associated with the order and address contains the shipping and contact address information.  

Saurv, I will see about gathering some sample data.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

PortletPaulfreelancerCommented:
>> I will see about gathering some sample data.
from this I hope:
basic order information, x_invoice contains the line items associated with the order and address contains the shipping and contact address information.  

could we see the query/proc that produces the temp table too?
0
rgolenorAuthor Commented:
Here is the query that is producing the temporary table:  Forgive anything silly in the query .. I'm self taught and do what I know works..

SELECT A.DEP, A.DOC_ALIAS, A.DOC_NO, A.DATE_FLD AS SCHEDULED_DATE, A.ORDER_DATE, A.PO AS CUST_PO, B.ITEM_CODE, B.NOTE, 
A.SHIPPED, B.ITEM_QTY, B.QTY_SHIP AS QTY_SHIPPED, C.NAME AS SHIP_NAME, C.STREET_ADDRESS AS SHIP_ADDRESS, C.CITY AS SHIP_CITY, 
C.STATE AS SHIP_STATE, C.ZIP AS SHIP_ZIP, C.COUNTRY AS SHIP_COUNTRY, A.DELIV_METH, A.CUSTMEMO4 AS AG_NOTES, A.CUSTCHAR9 
AS EXWORKS, B.SEQUENCE, A.CONT_CODE
	INTO A1_AG
	FROM INVOICES A, X_INVOIC B, ADDRESS C
WHERE A.STATUS = 8 AND A.DATE_FLD >= DATEADD (DAY, -60, GETDATE( ))AND A.ORDER_NO = B.ORDER_NO AND A.STATUS = B.STATUS 
AND A.SHIP_CODE = C.ADDR_CODE AND A.DOC_ALIAS = 'AG SALES ORDER' AND A.DEP = 'AG'
ORDER BY A.DOC_NO, B.SEQUENCE ASC

-->>ADD THE S.O. CONTACT INFORMATION
SELECT * 
	INTO A2_AG
FROM A1_AG A LEFT OUTER JOIN ADDRESS B
 ON A.CONT_CODE = B.ADDR_CODE

-->>GRAB THE COLUMNS WE WANT (ADD CONTACT INFO TO LIST)
SELECT DEP, DOC_ALIAS, DOC_NO, SCHEDULED_DATE, ORDER_DATE, AG_NOTES, ITEM_CODE, NOTE, ITEM_QTY AS QTY_ORDERED, QTY_SHIPPED, DELIV_METH,  
CUST_PO, SHIP_NAME, SHIP_ADDRESS, SHIP_CITY, SHIP_STATE, SHIP_ZIP, SHIP_COUNTRY, NAME AS CONTACT_NAME, STREET_ADDRESS AS CONTACT_ADDRESS,
CITY AS CONTACT_CITY, STATE AS CONTACT_STATE, ZIP AS CONTACT_ZIP, COUNTRY AS CONTACT_COUNTRY, EXWORKS, SEQUENCE
	INTO A3_AG 
	FROM A2_AG
ORDER BY DOC_NO, SEQUENCE ASC

-->>GRAB ITEMS TABLE DATA FOR ITEM DESCRIPTIONS
SELECT * 
	INTO A4_AG
FROM A3_AG A LEFT OUTER JOIN ITEMS B
 ON A.ITEM_CODE = B.ITEMNO

-->>GRAB THE COLUMNS WE WANT (ADD DESCRIPTIONS TO LIST)
SELECT DEP, DOC_ALIAS, DOC_NO, SCHEDULED_DATE, ORDER_DATE, AG_NOTES, ITEM_CODE, NOTE, DESCRIPT, QTY_ORDERED, QTY_SHIPPED, DELIV_METH,  
CUST_PO, SHIP_NAME, SHIP_ADDRESS, SHIP_CITY, SHIP_STATE, SHIP_ZIP, SHIP_COUNTRY, CONTACT_NAME, CONTACT_ADDRESS, CONTACT_CITY, 
CONTACT_STATE, CONTACT_ZIP, CONTACT_COUNTRY, EXWORKS, SEQUENCE
	INTO A5_AG
	FROM A4_AG

-->>ADD THE DESCRIPTIONS TO THE PART NUMBERS (WE ARE ONLY KEEPING THE NOTES COLUMN - DROPPING DESCRIPT)
UPDATE A5_AG
SET NOTE = DESCRIPT
WHERE ITEM_CODE IS NOT NULL AND NOTE IS NULL

-->>GRAB THE COLUMNS WE WANT (DROPPING DESCRIPT) AND ORDERING THE ORDERS
SELECT DEP, DOC_ALIAS, DOC_NO, SCHEDULED_DATE, ORDER_DATE, AG_NOTES, ITEM_CODE, NOTE, QTY_ORDERED, QTY_SHIPPED, DELIV_METH,  
CUST_PO, SHIP_NAME, SHIP_ADDRESS, SHIP_CITY, SHIP_STATE, SHIP_ZIP, SHIP_COUNTRY, CONTACT_NAME, CONTACT_ADDRESS, CONTACT_CITY, 
CONTACT_STATE, CONTACT_ZIP, CONTACT_COUNTRY, EXWORKS, SEQUENCE
INTO A6_AG
FROM A5_AG
ORDER BY DOC_NO, SEQUENCE ASC

-->>CLEAR EMPTY SO LINES
DELETE FROM A6_AG
WHERE ITEM_CODE IS NULL AND NOTE IS NULL OR ITEM_CODE LIKE '' AND NOTE LIKE ''

-->>clear the tables no longer needed
DROP TABLE A1_AG, A2_AG, A3_AG, A4_AG, A5_AG

Open in new window

0
PortletPaulfreelancerCommented:
Thanks for that code - it certainly helps me understand what's happening 'under the hood' as it were.

The conundrum here is that you have 'flattened' the data into a single table, but you are requesting a 'nested' (hierarchy) as an output; and these are at opposite ends of the spectrum.

I :think: the following might help generate some sample data. We don't need much in any table but all tables should have some and ideally represent something that would allow a result like the one in your question - this might be achieved by reducing the number of days allowed in the where clause, you will see that indicated below. The insert into table names may be changed but they should reflect the base table names. Hope this helps.

& note you know this data better than I do, if I missed anything please include it.
SELECT
        A.ORDER_NO
      , A.STATUS
      , A.SHIP_CODE
      , A.DEP
      , A.CONT_CODE
      , A.CUSTCHAR9      --AS EXWORKS
      , A.CUSTMEMO4      --AS AG_NOTES
      , A.DATE_FLD       --AS SCHEDULED_DATE
      , A.DELIV_METH
      , A.DOC_ALIAS
      , A.DOC_NO
      , A.ORDER_DATE
      , A.PO             --AS CUST_PO
      , A.SHIPPED
INTO XM_INVOICES
FROM INVOICES A
--INNER JOIN X_INVOIC B ON A.ORDER_NO = B.ORDER_NO AND A.STATUS = B.STATUS
--INNER JOIN ADDRESS C ON A.SHIP_CODE = C.ADDR_CODE
WHERE A.STATUS = 8
        AND A.DATE_FLD >= DATEADD(DAY, -1, GETDATE()) -- reduce from 60, we just want a small sample
        AND A.DOC_ALIAS = 'AG SALES ORDER'
        AND A.DEP = 'AG'




SELECT
        B.ORDER_NO
      , B.STATUS
      , B.ITEM_CODE
      , B.NOTE
      , B.ITEM_QTY
      , B.QTY_SHIP      --AS QTY_SHIPPED
INTO XM_X_INVOIC
FROM INVOICES A
INNER JOIN X_INVOIC B ON A.ORDER_NO = B.ORDER_NO AND A.STATUS = B.STATUS
--INNER JOIN ADDRESS C ON A.SHIP_CODE = C.ADDR_CODE
WHERE A.STATUS = 8
        AND A.DATE_FLD >= DATEADD(DAY, -1, GETDATE()) -- reduce from 60, we just want a small sample
        AND A.DOC_ALIAS = 'AG SALES ORDER'
        AND A.DEP = 'AG'




-- NB the following is a union all
SELECT
        C.ADDR_CODE
      , C.NAME           --AS SHIP_NAME
      , C.STREET_ADDRESS --AS SHIP_ADDRESS
      , C.CITY           --AS SHIP_CITY
      , C.STATE          --AS SHIP_STATE
      , C.ZIP            --AS SHIP_ZIP
      , C.COUNTRY        --AS SHIP_COUNTRY
INTO XM_ADDRESS
FROM INVOICES A
--INNER JOIN X_INVOIC B ON A.ORDER_NO = B.ORDER_NO AND A.STATUS = B.STATUS
INNER JOIN ADDRESS C ON A.SHIP_CODE = C.ADDR_CODE
WHERE A.STATUS = 8
        AND A.DATE_FLD >= DATEADD(DAY, -1, GETDATE()) -- reduce from 60, we just want a small sample
        AND A.DOC_ALIAS = 'AG SALES ORDER'
        AND A.DEP = 'AG'

UNION ALL

SELECT
        C.ADDR_CODE
      , C.NAME           --AS CONTACT_NAME
      , C.STREET_ADDRESS --AS CONTACT_ADDRESS
      , C.CITY           --AS CONTACT_CITY
      , C.STATE          --AS CONTACT_STATE
      , C.ZIP            --AS CONTACT_ZIP
      , C.COUNTRY        --AS CONTACT_COUNTRY
FROM INVOICES A
--INNER JOIN X_INVOIC B ON A.ORDER_NO = B.ORDER_NO AND A.STATUS = B.STATUS
INNER JOIN ADDRESS C ON A.CONT_CODE = C.ADDR_CODE
WHERE A.STATUS = 8
        AND A.DATE_FLD >= DATEADD(DAY, -1, GETDATE()) -- reduce from 60, we just want a small sample
        AND A.DOC_ALIAS = 'AG SALES ORDER'
        AND A.DEP = 'AG'




-->>GRAB ITEMS TABLE DATA FOR ITEM DESCRIPTIONS
SELECT
        B.* 
INTO XM_ITEMS
FROM XM_X_INVOIC A
INNER JOIN ITEMS B
        ON A.ITEM_CODE = B.ITEMNO

Open in new window

If there is any 'private' data amongst this e.g. a contact name then substitute a constant for the actual field, like this:
SELECT
        C.ADDR_CODE
      , 'C.NAME'           AS NAME           -- use real field names, not aliases
      , 'C.STREET_ADDRESS' AS STREET_ADDRESS -- use real field names, not aliases
      , C.CITY
      , C.STATE
      , C.ZIP
      , C.COUNTRY
INTO XM_ADDRESS
FROM INVOICES A
--INNER JOIN X_INVOIC B ON A.ORDER_NO = B.ORDER_NO AND A.STATUS = B.STATUS
INNER JOIN ADDRESS C ON A.SHIP_CODE = C.ADDR_CODE
WHERE A.STATUS = 8
        AND A.DATE_FLD >= DATEADD(DAY, -1, GETDATE()) -- reduce from 60, we just want a small sample
        AND A.DOC_ALIAS = 'AG SALES ORDER'
        AND A.DEP = 'AG'

Open in new window

but please don't do this on anything used in a join - normally only strings would need such treatment and those details aren't important to the process of producing the wanted output.
0
rgolenorAuthor Commented:
Paul, I've attached a file with sample data.   Note, that the address table contains 2 types of addresses that are indicted by status number (contact and ship).  I also added the sequence column to x_invoic as it is needed to format the lines appropriately (given that notes are placed above and below items at times).   Also note that not all orders will have contact information.  Thank for you help.
xm-sample-data.xls
0
rgolenorAuthor Commented:
Note: the sample data has been scrubbed for privacy.  The actual data was replaced with fictitious data.
0
PortletPaulfreelancerCommented:
Great, I have (I think) managed to get the sample data working. I had to fiddle with the items.itemno as there didn't seem to be any valid relationships for those. You can inspect that sample data here:  http://sqlfiddle.com/#!3/30b26/3
**MS SQL Server 2008 Schema Setup**:

    CREATE TABLE INVOICES
    	([ORDER_NO] int, [STATUS] int, [SHIP_CODE] int, [DEP] varchar(2), [CONT_CODE] varchar(6), [CUSTCHAR9] varchar(22), [CUSTMEMO4] varchar(22), [DATE_FLD] datetime, [DELIV_METH] varchar(9), [DOC_ALIAS] varchar(14), [DOC_NO] int, [ORDER_DATE] datetime, [PO] varchar(8), [SHIPPED] varchar(1))
    ;
    	
    INSERT INTO INVOICES
    	([ORDER_NO], [STATUS], [SHIP_CODE], [DEP], [CONT_CODE], [CUSTCHAR9], [CUSTMEMO4], [DATE_FLD], [DELIV_METH], [DOC_ALIAS], [DOC_NO], [ORDER_DATE], [PO], [SHIPPED])
    VALUES
    	(121370, 8, 137495, 'AG', '337495', 'EXW / EXWORKS Lakewood', 'WO 121470', '2013-09-30 00:00:00', 'UPSGround', 'AG SALES ORDER', 121370, '2013-09-30 00:00:00', '3454', 'F'),
    	(121372, 8, 136538, 'AG', '336538', 'EXW / EXWORKS Lakewood', NULL, '2013-09-30 00:00:00', 'UPSGround', 'AG SALES ORDER', 121372, '2013-09-30 00:00:00', '302780-A', 'F'),
    	(121381, 8, 138383, 'AG', NULL, 'EXW / EXWORKS Lakewood', 'need weights and dims.', '2013-10-01 00:00:00', 'TBD', 'AG SALES ORDER', 121381, '2013-10-01 00:00:00', '6811', 'F'),
    	(121383, 8, 138207, 'AG', NULL, 'EXW / EXWORKS Lakewood', NULL, '2013-10-01 00:00:00', 'WillCall', 'AG SALES ORDER', 121383, '2013-10-01 00:00:00', '2926', 'F'),
    	(121386, 8, 137495, 'AG', NULL, 'EXW / EXWORKS Lakewood', 'WO 122386 / Mapcargo', '2013-10-01 00:00:00', 'WillCall', 'AG SALES ORDER', 121386, '2013-10-01 00:00:00', '3465', 'F')
    ;
    
    CREATE TABLE X_INVOIC
    	([ORDER_NO] int, [STATUS] int, [ITEM_CODE] varchar(15), [NOTE] varchar(30), [ITEM_QTY] varchar(4), [QTY_SHIP] varchar(4), [SEQUENCE] int)
    ;
    	
    INSERT INTO X_INVOIC
    	([ORDER_NO], [STATUS], [ITEM_CODE], [NOTE], [ITEM_QTY], [QTY_SHIP], [SEQUENCE])
    VALUES
    	(121370, 8, NULL, '6 MONTHS WARRANTY', '0', '0', 1),
    	(121370, 8, NULL, 'CONDITION: FACTORY REFURBISHED', NULL, NULL, 3),
    	(121370, 8, 'WD7500BPKT-FR', NULL, '38', '0', 2),
    	(121372, 8, NULL, '90 DAY WARRANTY', '0', '0', 1),
    	(121372, 8, NULL, 'CONDITION: FACTORY REFURBISHED', NULL, NULL, 3),
    	(121372, 8, NULL, 'CONDITION: FACTORY REFURBISHED', NULL, NULL, 5),
    	(121372, 8, 'ST31000340NS-FR', NULL, '5', '0', 4),
    	(121372, 8, 'WD2500AAJS-FR', NULL, '20', '0', 2),
    	(121381, 8, NULL, 'CONDITION: FACTORY REFURBISHED', NULL, NULL, 3),
    	(121381, 8, NULL, NULL, '0', '0', 4),
    	(121381, 8, NULL, '6 month warranty', '0', '0', 1),
    	(121381, 8, 'ST380215A-SFR', NULL, '500', '0', 2),
    	(121383, 8, NULL, 'CONDITION: FACTORY REFURBISHED', NULL, NULL, 3),
    	(121383, 8, NULL, 'CONDITION: FACTORY REFURBISHED', NULL, NULL, 5),
    	(121383, 8, NULL, 'CONDITION: FACTORY REFURBISHED', NULL, NULL, 7),
    	(121383, 8, NULL, '6 month warranty', '0', '0', 1),
    	(121383, 8, 'ST31000340NS-FR', NULL, '260', '0', 4),
    	(121383, 8, 'W0A57911', NULL, '200', '0', 2),
    	(121383, 8, 'WDBAAF0020HBK', NULL, '85', '0', 6),
    	(121386, 8, NULL, '6 MONTHS WARRANTY', '0', '0', 1),
    	(121386, 8, NULL, 'CONDITION: FACTORY REFURBISHED', NULL, NULL, 4),
    	(121386, 8, NULL, NULL, '0', '0', 2),
    	(121386, 8, 'W0J11563', NULL, '222', '0', 3)
    ;
    
    CREATE TABLE ITEMS
    	([ACTIVE] varchar(1), [AUTOSERIAL] varchar(1), [ITEMNO] varchar(15), [CATEGORY] varchar(8), [DESCRIPT] varchar(36), [Q_ON_ORDER] int, [Q_ON_RESER] int, [QTY_STK] int)
    ;
    	
    INSERT INTO ITEMS
    	([ACTIVE], [AUTOSERIAL], [ITEMNO], [CATEGORY], [DESCRIPT], [Q_ON_ORDER], [Q_ON_RESER], [QTY_STK])
    VALUES
    	('T', 'T', 'ST31000340NS-FR', 'SATA2.5', '50GB 5400RPM 2.5" 8MB SATA 9.5MM', 0, 1222, 38),
    	('T', 'T', 'ST380215A-SFR', 'SATA2.5', '180GB 5400RPM 2.5" 8MB SATA 9.5MM', 0, 451, 784),
    	('T', 'T', 'W0A57911', 'EXTERNAL', '2TB 3.5" USB2.0 EXTERNAL COLOR:BLACK', 70, 85, 95),
    	('T', 'T', 'W0J11563', 'SATA', '750GB 7200RPM 2.5" 16MB SATA 3GBs', 500, 38, 7357),
    	('T', 'T', 'WD2500AAJS-FR', 'SATA', '250GB 7200RPM 3.5" 8MB SATA 3GBs', 0, 20, 4373),
    	('T', 'T', 'WD7500BPKT-FR', 'ATA3.5', '80GB 7200RPM 3.5" 2MB ATA100', 0, 500, 1608),
    	('T', 'T', 'WDBAAF0020HBK', 'SATA', '1TB 7200RPM 3.5" 32MB SATA 3GBs', 0, 3535, 4373)
    ;
    
    
    CREATE TABLE ADDRESS
    	([ADDR_CODE] int, [NAME] varchar(28), [STREET_ADDRESS] varchar(21), [CITY] varchar(16), [STATE] varchar(2), [ZIP] int, [COUNTRY] varchar(13), [STATUS] int)
    ;
    	
    INSERT INTO ADDRESS
    	([ADDR_CODE], [NAME], [STREET_ADDRESS], [CITY], [STATE], [ZIP], [COUNTRY], [STATUS])
    VALUES
    	(137495, 'Graphic Automation Ltd', '556 Hills Summit', 'WESTLAKE VILLAGE', 'CA', 91362, 'UNITED STATES', 5),
    	(136538, 'Profit Software Tech', '9512 Small Crest', 'MILTON', 'NH', 03851, 'UNITED STATES', 5),
    	(138383, 'Global Computation Resources', '1954 Noble Hollow', 'BELL Gardens', 'CA', 90201, 'UNITED STATES', 5),
    	(138207, 'Internal Opportunity Studio', '1654 Big Prairie Cove', 'AGOURA HILLS', 'CA', 91301, 'UNITED STATES', 5),
    	(137495, 'North American Memory', '785 Golden Dell', 'AGOURA HILLS', 'CA', 91301, 'UNITED STATES', 5),
    	(337495, 'Graphic Automation Ltd', '556 Hills Summit', 'WESTLAKE VILLAGE', 'CA', 91362, 'UNITED STATES', 6),
    	(336538, 'Profit Software Tech', '9512 Small Crest', 'MILTON', 'NH', 03851, 'UNITED STATES', 6),
    	(338383, 'Global Computation Resources', '1954 Noble Hollow', 'BELL Gardens', 'CA', 90201, 'UNITED STATES', 6),
    	(338207, 'Internal Opportunity Studio', '1654 Big Prairie Cove', 'AGOURA HILLS', 'CA', 91301, 'UNITED STATES', 6),
    	(337495, 'North American Memory', '785 Golden Dell', 'AGOURA HILLS', 'CA', 91301, 'UNITED STATES', 6)
    ;

Open in new window

You might find this query useful by the way.
**Query 1**:

    /*
    Order 1 
       Ship info
           ship name, address etc
       Contact info
           contact name, address etc
      Order details
          order date
          item 1 
          item 2
          item 3
          exworks
          op_notes
    */
          
    SELECT
            I.ORDER_NO
          , I.STATUS
          , I.SHIP_CODE
          , I.DEP
          , I.CONT_CODE
          , I.CUSTCHAR9      AS EXWORKS
          , I.CUSTMEMO4      AS AG_NOTES
          , convert(varchar(10),I.DATE_FLD,121)       AS SCHEDULED_DATE
          , I.DELIV_METH
          , I.DOC_ALIAS
          , I.DOC_NO
          , convert(varchar(10),I.ORDER_DATE,121)     AS ORDER_DATE
          , I.PO             AS CUST_PO
          , I.SHIPPED
          , X.ORDER_NO                    as x_ord_no --join
          , X.STATUS
          , X.ITEM_CODE
          , X.NOTE
          , X.ITEM_QTY
          , X.QTY_SHIP       AS QTY_SHIPPED
          , S.ADDR_CODE                   as s_addr_code --join
          , S.NAME           AS SHIP_NAME
          , S.STREET_ADDRESS AS SHIP_ADDRESS
          , S.CITY           AS SHIP_CITY
          , S.STATE          AS SHIP_STATE
          , S.ZIP            AS SHIP_ZIP
          , S.COUNTRY        AS SHIP_COUNTRY
          , C.ADDR_CODE                     as c_addr_code --join
          , C.NAME           AS CONTACT_NAME
          , C.STREET_ADDRESS AS CONTACT_ADDRESS
          , C.CITY           AS CONTACT_CITY
          , C.STATE          AS CONTACT_STATE
          , C.ZIP            AS CONTACT_ZIP
          , C.COUNTRY        AS CONTACT_COUNTRY
          , M.ACTIVE
          , M.AUTOSERIAL
          , M.ITEMNO
          , M.CATEGORY
          , M.DESCRIPT
          , M.Q_ON_ORDER
          , M.Q_ON_RESER
          , M.QTY_STK
    FROM INVOICES I
    INNER JOIN X_INVOIC X ON I.ORDER_NO = X.ORDER_NO AND I.STATUS = X.STATUS
    LEFT JOIN ADDRESS S   ON I.SHIP_CODE = S.ADDR_CODE
    LEFT JOIN ADDRESS C   ON I.CONT_CODE = C.ADDR_CODE
    LEFT JOIN ITEMS M     ON X.ITEM_CODE = M.ITEMNO
    WHERE I.STATUS = 8
            --AND I.DATE_FLD >= DATEADD(DAY, -1, GETDATE()) -- reduce from 60, we just want a small sample
            AND I.DOC_ALIAS = 'AG SALES ORDER'
            AND I.DEP = 'AG' 
    

**[Results][2]**:
    
    | ORDER_NO | STATUS | SHIP_CODE | DEP | CONT_CODE |                EXWORKS |               AG_NOTES | SCHEDULED_DATE | DELIV_METH |      DOC_ALIAS | DOC_NO | ORDER_DATE |  CUST_PO | SHIPPED | X_ORD_NO |       ITEM_CODE |                           NOTE | ITEM_QTY | QTY_SHIPPED | S_ADDR_CODE |                    SHIP_NAME |          SHIP_ADDRESS |        SHIP_CITY | SHIP_STATE | SHIP_ZIP |  SHIP_COUNTRY | C_ADDR_CODE |           CONTACT_NAME |  CONTACT_ADDRESS |     CONTACT_CITY | CONTACT_STATE | CONTACT_ZIP | CONTACT_COUNTRY | ACTIVE | AUTOSERIAL |          ITEMNO | CATEGORY |                             DESCRIPT | Q_ON_ORDER | Q_ON_RESER | QTY_STK |
    |----------|--------|-----------|-----|-----------|------------------------|------------------------|----------------|------------|----------------|--------|------------|----------|---------|----------|-----------------|--------------------------------|----------|-------------|-------------|------------------------------|-----------------------|------------------|------------|----------|---------------|-------------|------------------------|------------------|------------------|---------------|-------------|-----------------|--------|------------|-----------------|----------|--------------------------------------|------------|------------|---------|
    |   121370 |      8 |    137495 |  AG |    337495 | EXW / EXWORKS Lakewood |              WO 121470 |     2013-09-30 |  UPSGround | AG SALES ORDER | 121370 | 2013-09-30 |     3454 |       F |   121370 |          (null) |              6 MONTHS WARRANTY |        0 |           0 |      137495 |       Graphic Automation Ltd |      556 Hills Summit | WESTLAKE VILLAGE |         CA |    91362 | UNITED STATES |      337495 | Graphic Automation Ltd | 556 Hills Summit | WESTLAKE VILLAGE |            CA |       91362 |   UNITED STATES | (null) |     (null) |          (null) |   (null) |                               (null) |     (null) |     (null) |  (null) |
    |   121370 |      8 |    137495 |  AG |    337495 | EXW / EXWORKS Lakewood |              WO 121470 |     2013-09-30 |  UPSGround | AG SALES ORDER | 121370 | 2013-09-30 |     3454 |       F |   121370 |          (null) |              6 MONTHS WARRANTY |        0 |           0 |      137495 |       Graphic Automation Ltd |      556 Hills Summit | WESTLAKE VILLAGE |         CA |    91362 | UNITED STATES |      337495 |  North American Memory |  785 Golden Dell |     AGOURA HILLS |            CA |       91301 |   UNITED STATES | (null) |     (null) |          (null) |   (null) |                               (null) |     (null) |     (null) |  (null) |
    |   121370 |      8 |    137495 |  AG |    337495 | EXW / EXWORKS Lakewood |              WO 121470 |     2013-09-30 |  UPSGround | AG SALES ORDER | 121370 | 2013-09-30 |     3454 |       F |   121370 |          (null) |              6 MONTHS WARRANTY |        0 |           0 |      137495 |        North American Memory |       785 Golden Dell |     AGOURA HILLS |         CA |    91301 | UNITED STATES |      337495 | Graphic Automation Ltd | 556 Hills Summit | WESTLAKE VILLAGE |            CA |       91362 |   UNITED STATES | (null) |     (null) |          (null) |   (null) |                               (null) |     (null) |     (null) |  (null) |
    |   121370 |      8 |    137495 |  AG |    337495 | EXW / EXWORKS Lakewood |              WO 121470 |     2013-09-30 |  UPSGround | AG SALES ORDER | 121370 | 2013-09-30 |     3454 |       F |   121370 |          (null) |              6 MONTHS WARRANTY |        0 |           0 |      137495 |        North American Memory |       785 Golden Dell |     AGOURA HILLS |         CA |    91301 | UNITED STATES |      337495 |  North American Memory |  785 Golden Dell |     AGOURA HILLS |            CA |       91301 |   UNITED STATES | (null) |     (null) |          (null) |   (null) |                               (null) |     (null) |     (null) |  (null) |
    |   121370 |      8 |    137495 |  AG |    337495 | EXW / EXWORKS Lakewood |              WO 121470 |     2013-09-30 |  UPSGround | AG SALES ORDER | 121370 | 2013-09-30 |     3454 |       F |   121370 |          (null) | CONDITION: FACTORY REFURBISHED |   (null) |      (null) |      137495 |       Graphic Automation Ltd |      556 Hills Summit | WESTLAKE VILLAGE |         CA |    91362 | UNITED STATES |      337495 | Graphic Automation Ltd | 556 Hills Summit | WESTLAKE VILLAGE |            CA |       91362 |   UNITED STATES | (null) |     (null) |          (null) |   (null) |                               (null) |     (null) |     (null) |  (null) |
    |   121370 |      8 |    137495 |  AG |    337495 | EXW / EXWORKS Lakewood |              WO 121470 |     2013-09-30 |  UPSGround | AG SALES ORDER | 121370 | 2013-09-30 |     3454 |       F |   121370 |          (null) | CONDITION: FACTORY REFURBISHED |   (null) |      (null) |      137495 |       Graphic Automation Ltd |      556 Hills Summit | WESTLAKE VILLAGE |         CA |    91362 | UNITED STATES |      337495 |  North American Memory |  785 Golden Dell |     AGOURA HILLS |            CA |       91301 |   UNITED STATES | (null) |     (null) |          (null) |   (null) |                               (null) |     (null) |     (null) |  (null) |
    |   121370 |      8 |    137495 |  AG |    337495 | EXW / EXWORKS Lakewood |              WO 121470 |     2013-09-30 |  UPSGround | AG SALES ORDER | 121370 | 2013-09-30 |     3454 |       F |   121370 |          (null) | CONDITION: FACTORY REFURBISHED |   (null) |      (null) |      137495 |        North American Memory |       785 Golden Dell |     AGOURA HILLS |         CA |    91301 | UNITED STATES |      337495 | Graphic Automation Ltd | 556 Hills Summit | WESTLAKE VILLAGE |            CA |       91362 |   UNITED STATES | (null) |     (null) |          (null) |   (null) |                               (null) |     (null) |     (null) |  (null) |
    |   121370 |      8 |    137495 |  AG |    337495 | EXW / EXWORKS Lakewood |              WO 121470 |     2013-09-30 |  UPSGround | AG SALES ORDER | 121370 | 2013-09-30 |     3454 |       F |   121370 |          (null) | CONDITION: FACTORY REFURBISHED |   (null) |      (null) |      137495 |        North American Memory |       785 Golden Dell |     AGOURA HILLS |         CA |    91301 | UNITED STATES |      337495 |  North American Memory |  785 Golden Dell |     AGOURA HILLS |            CA |       91301 |   UNITED STATES | (null) |     (null) |          (null) |   (null) |                               (null) |     (null) |     (null) |  (null) |
    |   121370 |      8 |    137495 |  AG |    337495 | EXW / EXWORKS Lakewood |              WO 121470 |     2013-09-30 |  UPSGround | AG SALES ORDER | 121370 | 2013-09-30 |     3454 |       F |   121370 |   WD7500BPKT-FR |                         (null) |       38 |           0 |      137495 |       Graphic Automation Ltd |      556 Hills Summit | WESTLAKE VILLAGE |         CA |    91362 | UNITED STATES |      337495 | Graphic Automation Ltd | 556 Hills Summit | WESTLAKE VILLAGE |            CA |       91362 |   UNITED STATES |      T |          T |   WD7500BPKT-FR |   ATA3.5 |         80GB 7200RPM 3.5" 2MB ATA100 |          0 |        500 |    1608 |
    |   121370 |      8 |    137495 |  AG |    337495 | EXW / EXWORKS Lakewood |              WO 121470 |     2013-09-30 |  UPSGround | AG SALES ORDER | 121370 | 2013-09-30 |     3454 |       F |   121370 |   WD7500BPKT-FR |                         (null) |       38 |           0 |      137495 |       Graphic Automation Ltd |      556 Hills Summit | WESTLAKE VILLAGE |         CA |    91362 | UNITED STATES |      337495 |  North American Memory |  785 Golden Dell |     AGOURA HILLS |            CA |       91301 |   UNITED STATES |      T |          T |   WD7500BPKT-FR |   ATA3.5 |         80GB 7200RPM 3.5" 2MB ATA100 |          0 |        500 |    1608 |
    |   121370 |      8 |    137495 |  AG |    337495 | EXW / EXWORKS Lakewood |              WO 121470 |     2013-09-30 |  UPSGround | AG SALES ORDER | 121370 | 2013-09-30 |     3454 |       F |   121370 |   WD7500BPKT-FR |                         (null) |       38 |           0 |      137495 |        North American Memory |       785 Golden Dell |     AGOURA HILLS |         CA |    91301 | UNITED STATES |      337495 | Graphic Automation Ltd | 556 Hills Summit | WESTLAKE VILLAGE |            CA |       91362 |   UNITED STATES |      T |          T |   WD7500BPKT-FR |   ATA3.5 |         80GB 7200RPM 3.5" 2MB ATA100 |          0 |        500 |    1608 |
    |   121370 |      8 |    137495 |  AG |    337495 | EXW / EXWORKS Lakewood |              WO 121470 |     2013-09-30 |  UPSGround | AG SALES ORDER | 121370 | 2013-09-30 |     3454 |       F |   121370 |   WD7500BPKT-FR |                         (null) |       38 |           0 |      137495 |        North American Memory |       785 Golden Dell |     AGOURA HILLS |         CA |    91301 | UNITED STATES |      337495 |  North American Memory |  785 Golden Dell |     AGOURA HILLS |            CA |       91301 |   UNITED STATES |      T |          T |   WD7500BPKT-FR |   ATA3.5 |         80GB 7200RPM 3.5" 2MB ATA100 |          0 |        500 |    1608 |
    |   121372 |      8 |    136538 |  AG |    336538 | EXW / EXWORKS Lakewood |                 (null) |     2013-09-30 |  UPSGround | AG SALES ORDER | 121372 | 2013-09-30 | 302780-A |       F |   121372 |          (null) |                90 DAY WARRANTY |        0 |           0 |      136538 |         Profit Software Tech |      9512 Small Crest |           MILTON |         NH |     3851 | UNITED STATES |      336538 |   Profit Software Tech | 9512 Small Crest |           MILTON |            NH |        3851 |   UNITED STATES | (null) |     (null) |          (null) |   (null) |                               (null) |     (null) |     (null) |  (null) |
    |   121372 |      8 |    136538 |  AG |    336538 | EXW / EXWORKS Lakewood |                 (null) |     2013-09-30 |  UPSGround | AG SALES ORDER | 121372 | 2013-09-30 | 302780-A |       F |   121372 |          (null) | CONDITION: FACTORY REFURBISHED |   (null) |      (null) |      136538 |         Profit Software Tech |      9512 Small Crest |           MILTON |         NH |     3851 | UNITED STATES |      336538 |   Profit Software Tech | 9512 Small Crest |           MILTON |            NH |        3851 |   UNITED STATES | (null) |     (null) |          (null) |   (null) |                               (null) |     (null) |     (null) |  (null) |
    |   121372 |      8 |    136538 |  AG |    336538 | EXW / EXWORKS Lakewood |                 (null) |     2013-09-30 |  UPSGround | AG SALES ORDER | 121372 | 2013-09-30 | 302780-A |       F |   121372 |          (null) | CONDITION: FACTORY REFURBISHED |   (null) |      (null) |      136538 |         Profit Software Tech |      9512 Small Crest |           MILTON |         NH |     3851 | UNITED STATES |      336538 |   Profit Software Tech | 9512 Small Crest |           MILTON |            NH |        3851 |   UNITED STATES | (null) |     (null) |          (null) |   (null) |                               (null) |     (null) |     (null) |  (null) |
    |   121372 |      8 |    136538 |  AG |    336538 | EXW / EXWORKS Lakewood |                 (null) |     2013-09-30 |  UPSGround | AG SALES ORDER | 121372 | 2013-09-30 | 302780-A |       F |   121372 | ST31000340NS-FR |                         (null) |        5 |           0 |      136538 |         Profit Software Tech |      9512 Small Crest |           MILTON |         NH |     3851 | UNITED STATES |      336538 |   Profit Software Tech | 9512 Small Crest |           MILTON |            NH |        3851 |   UNITED STATES |      T |          T | ST31000340NS-FR |  SATA2.5 |     50GB 5400RPM 2.5" 8MB SATA 9.5MM |          0 |       1222 |      38 |
    |   121372 |      8 |    136538 |  AG |    336538 | EXW / EXWORKS Lakewood |                 (null) |     2013-09-30 |  UPSGround | AG SALES ORDER | 121372 | 2013-09-30 | 302780-A |       F |   121372 |   WD2500AAJS-FR |                         (null) |       20 |           0 |      136538 |         Profit Software Tech |      9512 Small Crest |           MILTON |         NH |     3851 | UNITED STATES |      336538 |   Profit Software Tech | 9512 Small Crest |           MILTON |            NH |        3851 |   UNITED STATES |      T |          T |   WD2500AAJS-FR |     SATA |     250GB 7200RPM 3.5" 8MB SATA 3GBs |          0 |         20 |    4373 |
    |   121381 |      8 |    138383 |  AG |    (null) | EXW / EXWORKS Lakewood | need weights and dims. |     2013-10-01 |        TBD | AG SALES ORDER | 121381 | 2013-10-01 |     6811 |       F |   121381 |          (null) | CONDITION: FACTORY REFURBISHED |   (null) |      (null) |      138383 | Global Computation Resources |     1954 Noble Hollow |     BELL Gardens |         CA |    90201 | UNITED STATES |      (null) |                 (null) |           (null) |           (null) |        (null) |      (null) |          (null) | (null) |     (null) |          (null) |   (null) |                               (null) |     (null) |     (null) |  (null) |
    |   121381 |      8 |    138383 |  AG |    (null) | EXW / EXWORKS Lakewood | need weights and dims. |     2013-10-01 |        TBD | AG SALES ORDER | 121381 | 2013-10-01 |     6811 |       F |   121381 |          (null) |                         (null) |        0 |           0 |      138383 | Global Computation Resources |     1954 Noble Hollow |     BELL Gardens |         CA |    90201 | UNITED STATES |      (null) |                 (null) |           (null) |           (null) |        (null) |      (null) |          (null) | (null) |     (null) |          (null) |   (null) |                               (null) |     (null) |     (null) |  (null) |
    |   121381 |      8 |    138383 |  AG |    (null) | EXW / EXWORKS Lakewood | need weights and dims. |     2013-10-01 |        TBD | AG SALES ORDER | 121381 | 2013-10-01 |     6811 |       F |   121381 |          (null) |               6 month warranty |        0 |           0 |      138383 | Global Computation Resources |     1954 Noble Hollow |     BELL Gardens |         CA |    90201 | UNITED STATES |      (null) |                 (null) |           (null) |           (null) |        (null) |      (null) |          (null) | (null) |     (null) |          (null) |   (null) |                               (null) |     (null) |     (null) |  (null) |
    |   121381 |      8 |    138383 |  AG |    (null) | EXW / EXWORKS Lakewood | need weights and dims. |     2013-10-01 |        TBD | AG SALES ORDER | 121381 | 2013-10-01 |     6811 |       F |   121381 |   ST380215A-SFR |                         (null) |      500 |           0 |      138383 | Global Computation Resources |     1954 Noble Hollow |     BELL Gardens |         CA |    90201 | UNITED STATES |      (null) |                 (null) |           (null) |           (null) |        (null) |      (null) |          (null) |      T |          T |   ST380215A-SFR |  SATA2.5 |    180GB 5400RPM 2.5" 8MB SATA 9.5MM |          0 |        451 |     784 |
    |   121383 |      8 |    138207 |  AG |    (null) | EXW / EXWORKS Lakewood |                 (null) |     2013-10-01 |   WillCall | AG SALES ORDER | 121383 | 2013-10-01 |     2926 |       F |   121383 |          (null) | CONDITION: FACTORY REFURBISHED |   (null) |      (null) |      138207 |  Internal Opportunity Studio | 1654 Big Prairie Cove |     AGOURA HILLS |         CA |    91301 | UNITED STATES |      (null) |                 (null) |           (null) |           (null) |        (null) |      (null) |          (null) | (null) |     (null) |          (null) |   (null) |                               (null) |     (null) |     (null) |  (null) |
    |   121383 |      8 |    138207 |  AG |    (null) | EXW / EXWORKS Lakewood |                 (null) |     2013-10-01 |   WillCall | AG SALES ORDER | 121383 | 2013-10-01 |     2926 |       F |   121383 |          (null) | CONDITION: FACTORY REFURBISHED |   (null) |      (null) |      138207 |  Internal Opportunity Studio | 1654 Big Prairie Cove |     AGOURA HILLS |         CA |    91301 | UNITED STATES |      (null) |                 (null) |           (null) |           (null) |        (null) |      (null) |          (null) | (null) |     (null) |          (null) |   (null) |                               (null) |     (null) |     (null) |  (null) |
    |   121383 |      8 |    138207 |  AG |    (null) | EXW / EXWORKS Lakewood |                 (null) |     2013-10-01 |   WillCall | AG SALES ORDER | 121383 | 2013-10-01 |     2926 |       F |   121383 |          (null) | CONDITION: FACTORY REFURBISHED |   (null) |      (null) |      138207 |  Internal Opportunity Studio | 1654 Big Prairie Cove |     AGOURA HILLS |         CA |    91301 | UNITED STATES |      (null) |                 (null) |           (null) |           (null) |        (null) |      (null) |          (null) | (null) |     (null) |          (null) |   (null) |                               (null) |     (null) |     (null) |  (null) |
    |   121383 |      8 |    138207 |  AG |    (null) | EXW / EXWORKS Lakewood |                 (null) |     2013-10-01 |   WillCall | AG SALES ORDER | 121383 | 2013-10-01 |     2926 |       F |   121383 |          (null) |               6 month warranty |        0 |           0 |      138207 |  Internal Opportunity Studio | 1654 Big Prairie Cove |     AGOURA HILLS |         CA |    91301 | UNITED STATES |      (null) |                 (null) |           (null) |           (null) |        (null) |      (null) |          (null) | (null) |     (null) |          (null) |   (null) |                               (null) |     (null) |     (null) |  (null) |
    |   121383 |      8 |    138207 |  AG |    (null) | EXW / EXWORKS Lakewood |                 (null) |     2013-10-01 |   WillCall | AG SALES ORDER | 121383 | 2013-10-01 |     2926 |       F |   121383 | ST31000340NS-FR |                         (null) |      260 |           0 |      138207 |  Internal Opportunity Studio | 1654 Big Prairie Cove |     AGOURA HILLS |         CA |    91301 | UNITED STATES |      (null) |                 (null) |           (null) |           (null) |        (null) |      (null) |          (null) |      T |          T | ST31000340NS-FR |  SATA2.5 |     50GB 5400RPM 2.5" 8MB SATA 9.5MM |          0 |       1222 |      38 |
    |   121383 |      8 |    138207 |  AG |    (null) | EXW / EXWORKS Lakewood |                 (null) |     2013-10-01 |   WillCall | AG SALES ORDER | 121383 | 2013-10-01 |     2926 |       F |   121383 |        W0A57911 |                         (null) |      200 |           0 |      138207 |  Internal Opportunity Studio | 1654 Big Prairie Cove |     AGOURA HILLS |         CA |    91301 | UNITED STATES |      (null) |                 (null) |           (null) |           (null) |        (null) |      (null) |          (null) |      T |          T |        W0A57911 | EXTERNAL | 2TB 3.5" USB2.0 EXTERNAL COLOR:BLACK |         70 |         85 |      95 |
    |   121383 |      8 |    138207 |  AG |    (null) | EXW / EXWORKS Lakewood |                 (null) |     2013-10-01 |   WillCall | AG SALES ORDER | 121383 | 2013-10-01 |     2926 |       F |   121383 |   WDBAAF0020HBK |                         (null) |       85 |           0 |      138207 |  Internal Opportunity Studio | 1654 Big Prairie Cove |     AGOURA HILLS |         CA |    91301 | UNITED STATES |      (null) |                 (null) |           (null) |           (null) |        (null) |      (null) |          (null) |      T |          T |   WDBAAF0020HBK |     SATA |      1TB 7200RPM 3.5" 32MB SATA 3GBs |          0 |       3535 |    4373 |
    |   121386 |      8 |    137495 |  AG |    (null) | EXW / EXWORKS Lakewood |   WO 122386 / Mapcargo |     2013-10-01 |   WillCall | AG SALES ORDER | 121386 | 2013-10-01 |     3465 |       F |   121386 |          (null) |              6 MONTHS WARRANTY |        0 |           0 |      137495 |       Graphic Automation Ltd |      556 Hills Summit | WESTLAKE VILLAGE |         CA |    91362 | UNITED STATES |      (null) |                 (null) |           (null) |           (null) |        (null) |      (null) |          (null) | (null) |     (null) |          (null) |   (null) |                               (null) |     (null) |     (null) |  (null) |
    |   121386 |      8 |    137495 |  AG |    (null) | EXW / EXWORKS Lakewood |   WO 122386 / Mapcargo |     2013-10-01 |   WillCall | AG SALES ORDER | 121386 | 2013-10-01 |     3465 |       F |   121386 |          (null) |              6 MONTHS WARRANTY |        0 |           0 |      137495 |        North American Memory |       785 Golden Dell |     AGOURA HILLS |         CA |    91301 | UNITED STATES |      (null) |                 (null) |           (null) |           (null) |        (null) |      (null) |          (null) | (null) |     (null) |          (null) |   (null) |                               (null) |     (null) |     (null) |  (null) |
    |   121386 |      8 |    137495 |  AG |    (null) | EXW / EXWORKS Lakewood |   WO 122386 / Mapcargo |     2013-10-01 |   WillCall | AG SALES ORDER | 121386 | 2013-10-01 |     3465 |       F |   121386 |          (null) | CONDITION: FACTORY REFURBISHED |   (null) |      (null) |      137495 |       Graphic Automation Ltd |      556 Hills Summit | WESTLAKE VILLAGE |         CA |    91362 | UNITED STATES |      (null) |                 (null) |           (null) |           (null) |        (null) |      (null) |          (null) | (null) |     (null) |          (null) |   (null) |                               (null) |     (null) |     (null) |  (null) |
    |   121386 |      8 |    137495 |  AG |    (null) | EXW / EXWORKS Lakewood |   WO 122386 / Mapcargo |     2013-10-01 |   WillCall | AG SALES ORDER | 121386 | 2013-10-01 |     3465 |       F |   121386 |          (null) | CONDITION: FACTORY REFURBISHED |   (null) |      (null) |      137495 |        North American Memory |       785 Golden Dell |     AGOURA HILLS |         CA |    91301 | UNITED STATES |      (null) |                 (null) |           (null) |           (null) |        (null) |      (null) |          (null) | (null) |     (null) |          (null) |   (null) |                               (null) |     (null) |     (null) |  (null) |
    |   121386 |      8 |    137495 |  AG |    (null) | EXW / EXWORKS Lakewood |   WO 122386 / Mapcargo |     2013-10-01 |   WillCall | AG SALES ORDER | 121386 | 2013-10-01 |     3465 |       F |   121386 |          (null) |                         (null) |        0 |           0 |      137495 |       Graphic Automation Ltd |      556 Hills Summit | WESTLAKE VILLAGE |         CA |    91362 | UNITED STATES |      (null) |                 (null) |           (null) |           (null) |        (null) |      (null) |          (null) | (null) |     (null) |          (null) |   (null) |                               (null) |     (null) |     (null) |  (null) |
    |   121386 |      8 |    137495 |  AG |    (null) | EXW / EXWORKS Lakewood |   WO 122386 / Mapcargo |     2013-10-01 |   WillCall | AG SALES ORDER | 121386 | 2013-10-01 |     3465 |       F |   121386 |          (null) |                         (null) |        0 |           0 |      137495 |        North American Memory |       785 Golden Dell |     AGOURA HILLS |         CA |    91301 | UNITED STATES |      (null) |                 (null) |           (null) |           (null) |        (null) |      (null) |          (null) | (null) |     (null) |          (null) |   (null) |                               (null) |     (null) |     (null) |  (null) |
    |   121386 |      8 |    137495 |  AG |    (null) | EXW / EXWORKS Lakewood |   WO 122386 / Mapcargo |     2013-10-01 |   WillCall | AG SALES ORDER | 121386 | 2013-10-01 |     3465 |       F |   121386 |        W0J11563 |                         (null) |      222 |           0 |      137495 |       Graphic Automation Ltd |      556 Hills Summit | WESTLAKE VILLAGE |         CA |    91362 | UNITED STATES |      (null) |                 (null) |           (null) |           (null) |        (null) |      (null) |          (null) |      T |          T |        W0J11563 |     SATA |    750GB 7200RPM 2.5" 16MB SATA 3GBs |        500 |         38 |    7357 |
    |   121386 |      8 |    137495 |  AG |    (null) | EXW / EXWORKS Lakewood |   WO 122386 / Mapcargo |     2013-10-01 |   WillCall | AG SALES ORDER | 121386 | 2013-10-01 |     3465 |       F |   121386 |        W0J11563 |                         (null) |      222 |           0 |      137495 |        North American Memory |       785 Golden Dell |     AGOURA HILLS |         CA |    91301 | UNITED STATES |      (null) |                 (null) |           (null) |           (null) |        (null) |      (null) |          (null) |      T |          T |        W0J11563 |     SATA |    750GB 7200RPM 2.5" 16MB SATA 3GBs |        500 |         38 |    7357 |



  [1]: http://sqlfiddle.com/#!3/30b26/3

  [2]: http://sqlfiddle.com/#!3/30b26/3/0

Open in new window

0
rgolenorAuthor Commented:
Thanks Paul for the Query1.. that is so much better than using all those temporary tables.  I ran it on the live data and it worked great.   I apologize if I messed something up with the sample data as I was trying to make changes to keep the data private.  

Does it look like it might be possible to nest the data in an xml output based on the sample data?  

Again, many thanks for your help and that sample query!
0
rgolenorAuthor Commented:
Paul, I'll check out the sql fiddle in the morning.. it's late for me now.
0
PortletPaulfreelancerCommented:
I have an admission to make, forming xml from sql isn't a strength of mine. but yes, it can certainly be done.

My principal concern was that you provided a sample schema and data that was not 'flat' because that is best for the xml you need.

I'm hoping other experts can chip in now with that xml piece
- but i'll plug away on the xml if no-one else can whip it up.
0
PortletPaulfreelancerCommented:
Well, don't get too excited, but I can produce this (almost):
<ALL>
  <ORDER>
    <ORDER_NO>121370</ORDER_NO>
    <STATUS>8</STATUS>
    <DEP>AG</DEP>
    <EXWORKS>EXW / EXWORKS Lakewood</EXWORKS>
    <AG_NOTES>WO 121470</AG_NOTES>
    <SCHEDULED_DATE>2013-09-30T00:00:00</SCHEDULED_DATE>
    <DELIV_METH>UPSGround</DELIV_METH>
    <DOC_ALIAS>AG SALES ORDER</DOC_ALIAS>
    <DOC_NO>121370</DOC_NO>
    <ORDER_DATE>2013-09-30T00:00:00</ORDER_DATE>
    <CUST_PO>3454</CUST_PO>
    <SHIPPED>F</SHIPPED>
    <SHIP_CODE>137495</SHIP_CODE>
    <SHIP_NAME>Graphic Automation Ltd</SHIP_NAME>
    <SHIP_ADDRESS>556 Hills Summit</SHIP_ADDRESS>
    <SHIP_CITY>WESTLAKE VILLAGE</SHIP_CITY>
    <SHIP_STATE>CA</SHIP_STATE>
    <SHIP_ZIP>91362</SHIP_ZIP>
    <SHIP_COUNTRY>UNITED STATES</SHIP_COUNTRY>
    <CONTACT_CODE>337495</CONTACT_CODE>
    <CONTACT_NAME>Graphic Automation Ltd</CONTACT_NAME>
    <CONTACT_ADDRESS>556 Hills Summit</CONTACT_ADDRESS>
    <CONTACT_CITY>WESTLAKE VILLAGE</CONTACT_CITY>
    <CONTACT_STATE>CA</CONTACT_STATE>
    <CONTACT_ZIP>91362</CONTACT_ZIP>
    <CONTACT_COUNTRY>UNITED STATES</CONTACT_COUNTRY>
    <DETAILS>
      <ITEM_CODE>WD7500BPKT-FR</ITEM_CODE>
      <STATUS>8</STATUS>
      <ITEM_QTY>38</ITEM_QTY>
      <QTY_SHIPPED>0</QTY_SHIPPED>
      <ACTIVE>T</ACTIVE>
      <AUTOSERIAL>T</AUTOSERIAL>
      <ITEMNO>WD7500BPKT-FR</ITEMNO>
      <CATEGORY>ATA3.5</CATEGORY>
      <DESCRIPT>80GB 7200RPM 3.5" 2MB ATA100</DESCRIPT>
      <Q_ON_ORDER>0</Q_ON_ORDER>
      <Q_ON_RESER>500</Q_ON_RESER>
      <QTY_STK>1608</QTY_STK>
    </DETAILS>
  </ORDER>
  <ORDER>
    <ORDER_NO>121372</ORDER_NO>
    <STATUS>8</STATUS>
    <DEP>AG</DEP>
    <EXWORKS>EXW / EXWORKS Lakewood</EXWORKS>
    <SCHEDULED_DATE>2013-09-30T00:00:00</SCHEDULED_DATE>
    <DELIV_METH>UPSGround</DELIV_METH>
    <DOC_ALIAS>AG SALES ORDER</DOC_ALIAS>
    <DOC_NO>121372</DOC_NO>
    <ORDER_DATE>2013-09-30T00:00:00</ORDER_DATE>
    <CUST_PO>302780-A</CUST_PO>
    <SHIPPED>F</SHIPPED>
    <SHIP_CODE>136538</SHIP_CODE>
    <SHIP_NAME>Profit Software Tech</SHIP_NAME>
    <SHIP_ADDRESS>9512 Small Crest</SHIP_ADDRESS>
    <SHIP_CITY>MILTON</SHIP_CITY>
    <SHIP_STATE>NH</SHIP_STATE>
    <SHIP_ZIP>3851</SHIP_ZIP>
    <SHIP_COUNTRY>UNITED STATES</SHIP_COUNTRY>
    <CONTACT_CODE>336538</CONTACT_CODE>
    <CONTACT_NAME>Profit Software Tech</CONTACT_NAME>
    <CONTACT_ADDRESS>9512 Small Crest</CONTACT_ADDRESS>
    <CONTACT_CITY>MILTON</CONTACT_CITY>
    <CONTACT_STATE>NH</CONTACT_STATE>
    <CONTACT_ZIP>3851</CONTACT_ZIP>
    <CONTACT_COUNTRY>UNITED STATES</CONTACT_COUNTRY>
    <DETAILS>
      <ITEM_CODE>ST31000340NS-FR</ITEM_CODE>
      <STATUS>8</STATUS>
      <ITEM_QTY>5</ITEM_QTY>
      <QTY_SHIPPED>0</QTY_SHIPPED>
      <ACTIVE>T</ACTIVE>
      <AUTOSERIAL>T</AUTOSERIAL>
      <ITEMNO>ST31000340NS-FR</ITEMNO>
      <CATEGORY>SATA2.5</CATEGORY>
      <DESCRIPT>50GB 5400RPM 2.5" 8MB SATA 9.5MM</DESCRIPT>
      <Q_ON_ORDER>0</Q_ON_ORDER>
      <Q_ON_RESER>1222</Q_ON_RESER>
      <QTY_STK>38</QTY_STK>
    </DETAILS>
    <DETAILS>
      <ITEM_CODE>WD2500AAJS-FR</ITEM_CODE>
      <STATUS>8</STATUS>
      <ITEM_QTY>20</ITEM_QTY>
      <QTY_SHIPPED>0</QTY_SHIPPED>
      <ACTIVE>T</ACTIVE>
      <AUTOSERIAL>T</AUTOSERIAL>
      <ITEMNO>WD2500AAJS-FR</ITEMNO>
      <CATEGORY>SATA</CATEGORY>
      <DESCRIPT>250GB 7200RPM 3.5" 8MB SATA 3GBs</DESCRIPT>
      <Q_ON_ORDER>0</Q_ON_ORDER>
      <Q_ON_RESER>20</Q_ON_RESER>
      <QTY_STK>4373</QTY_STK>
    </DETAILS>
  </ORDER>
</ALL>

Open in new window

The "almost" is that "<ALL>" and "</ALL>" were introduced manually. That is, I can produce almost well formed xml, except for the first and last tag.

Anyway here's what I have so far:
SELECT
        I.ORDER_NO
      , I.STATUS
      , I.DEP
      , I.CUSTCHAR9      AS EXWORKS
      , I.CUSTMEMO4      AS AG_NOTES
      , I.DATE_FLD       AS SCHEDULED_DATE
      , I.DELIV_METH
      , I.DOC_ALIAS
      , I.DOC_NO
      , I.ORDER_DATE
      , I.PO             AS CUST_PO
      , I.SHIPPED
      ,    (
            SELECT TOP 1
                    S.ADDR_CODE      AS SHIP_CODE
                  , S.NAME           AS SHIP_NAME
                  , S.STREET_ADDRESS AS SHIP_ADDRESS
                  , S.CITY           AS SHIP_CITY
                  , S.STATE          AS SHIP_STATE
                  , S.ZIP            AS SHIP_ZIP
                  , S.COUNTRY        AS SHIP_COUNTRY
            FROM ADDRESS S 
            WHERE I.SHIP_CODE = S.ADDR_CODE
            FOR XML PATH ('')
            )
      ,    (
            SELECT TOP 1
                    C.ADDR_CODE      AS CONTACT_CODE
                  , C.NAME           AS CONTACT_NAME
                  , C.STREET_ADDRESS AS CONTACT_ADDRESS
                  , C.CITY           AS CONTACT_CITY
                  , C.STATE          AS CONTACT_STATE
                  , C.ZIP            AS CONTACT_ZIP
                  , C.COUNTRY        AS CONTACT_COUNTRY
            FROM ADDRESS C 
            WHERE I.CONT_CODE = C.ADDR_CODE
            FOR XML PATH ('')
            )
      ,    (
            SELECT
                    X.ITEM_CODE
                  , X.STATUS
                  , X.NOTE
                  , X.ITEM_QTY
                  , X.QTY_SHIP       AS QTY_SHIPPED
                  , M.ACTIVE
                  , M.AUTOSERIAL
                  , M.ITEMNO
                  , M.CATEGORY
                  , M.DESCRIPT
                  , M.Q_ON_ORDER
                  , M.Q_ON_RESER
                  , M.QTY_STK
            FROM X_INVOIC X
            INNER JOIN ITEMS M ON X.ITEM_CODE = M.ITEMNO
            WHERE I.ORDER_NO = X.ORDER_NO AND I.STATUS = X.STATUS
            FOR XML PATH ('DETAILS')
            )
FROM INVOICES I
WHERE I.STATUS = 8
        --AND I.DATE_FLD >= DATEADD(DAY, -1, GETDATE())
        AND I.DOC_ALIAS = 'AG SALES ORDER'
        AND I.DEP = 'AG'

        AND I.ORDER_NO in (121370,121372) -- USED FOR TESTING ONLY

FOR XML PATH ('ORDER')
;
-- http://sqlfiddle.com/#!3/30b26/49

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
Oh, I should add, I had to introduce "SELECT TOP 1" twice in the query above to limit ship-to and contact information to one each - may be an issue with the data but there seem to be more than one for an order. This may be important to note.
0
rgolenorAuthor Commented:
Thanks for the help and effort Paul.  I've attempted to run and tweak the code above.  I am stuck currently on there being no nested child level for order details.  After </shipped> I get &lt;SHIP_CODE&gt;134595&lt;/SHIP_CODE&gt;&lt;SHIP_NAME&gt; etc...    

I think if once I get the order details nested and figure out how to export the compiled data to an xml file I'll be golden.  

Thanks again for all your help.
0
PortletPaulfreelancerCommented:
Hi, I understand the frustration, I tried tweaking at one point ans also got those &gt; &lt; entities...

I'm going to throw this one at you, it looks like it might actually be helpful but I haven't tried it (yet anyway). I did start out using the 'explicit' method of tsql's xml - but it's just way too complex. This "trick" appears to be lots easier.

http://www.modhul.com/2008/01/23/an-easier-way-to-do-complex-for-xml-explicit/
0
Saurabh BhadauriaCommented:
Just a little change in above code..Hope it will work for you..

SELECT
        I.ORDER_NO
      , I.STATUS
      , I.DEP
      , I.CUSTCHAR9      AS EXWORKS
      , I.CUSTMEMO4      AS AG_NOTES
      , I.DATE_FLD       AS SCHEDULED_DATE
      , I.DELIV_METH
      , I.DOC_ALIAS
      , I.DOC_NO
      , I.ORDER_DATE
      , I.PO             AS CUST_PO
      , I.SHIPPED
      ,    (
            SELECT 
                    S.ADDR_CODE      AS SHIP_CODE
                  , S.NAME           AS SHIP_NAME
                  , S.STREET_ADDRESS AS SHIP_ADDRESS
                  , S.CITY           AS SHIP_CITY
                  , S.STATE          AS SHIP_STATE
                  , S.ZIP            AS SHIP_ZIP
                  , S.COUNTRY        AS SHIP_COUNTRY
            FROM ADDRESS S 
            WHERE I.SHIP_CODE = S.ADDR_CODE
            FOR XML PATH ('') , type
            )
      ,    (
            SELECT 
                    C.ADDR_CODE      AS CONTACT_CODE
                  , C.NAME           AS CONTACT_NAME
                  , C.STREET_ADDRESS AS CONTACT_ADDRESS
                  , C.CITY           AS CONTACT_CITY
                  , C.STATE          AS CONTACT_STATE
                  , C.ZIP            AS CONTACT_ZIP
                  , C.COUNTRY        AS CONTACT_COUNTRY
            FROM ADDRESS C 
            WHERE I.CONT_CODE = C.ADDR_CODE
            FOR XML PATH ('') , type
            )
      ,    (
            SELECT
                    X.ITEM_CODE
                  , X.STATUS
                  , X.NOTE
                  , X.ITEM_QTY
                  , X.QTY_SHIP       AS QTY_SHIPPED
                  , M.ACTIVE
                  , M.AUTOSERIAL
                  , M.ITEMNO
                  , M.CATEGORY
                  , M.DESCRIPT
                  , M.Q_ON_ORDER
                  , M.Q_ON_RESER
                  , M.QTY_STK
            FROM X_INVOIC X
            INNER JOIN ITEMS M ON X.ITEM_CODE = M.ITEMNO
            WHERE I.ORDER_NO = X.ORDER_NO AND I.STATUS = X.STATUS
            FOR XML PATH ('DETAILS') , type
            )
FROM INVOICES I
WHERE I.STATUS = 8
        --AND I.DATE_FLD >= DATEADD(DAY, -1, GETDATE())
        AND I.DOC_ALIAS = 'AG SALES ORDER'
        AND I.DEP = 'AG'

        AND I.ORDER_NO in (121370,121372) -- USED FOR TESTING ONLY

FOR XML PATH ('ORDER') , Root('All')
;

Open in new window

0
PortletPaulfreelancerCommented:
@Saurv
this still remains this: "no nested child level for order details"
and I'm not strong in the xml/sql area
any tips/clues/solution?

&
FOR XML PATH ('ORDER') , Root('All')

darn it, I went crazy trying to get a root node - BIG thank you!
(naturally it just had to be that obvious grrr)
0
PortletPaulfreelancerCommented:
OK, now that I know how to insert a root node (I used 'ORDERS' not 'ALL'), and changing 'DETAILS' to 'ORDER_DETAILS', below is the current output,

It should be noted that there is a mixture of one-to-one and one-to-many in your original format:
Order 1
   Ship info
       ship name, address etc
   Contact info
       contact name, address etc
  Order details
      order date -- << one-to-one
      item 1 -- << one-to-many
      item 2 -- << one-to-many
      item 3 -- << one-to-many
      exworks  -- << one-to-one
      op_notes -- not sure

Hence I'm not sure what the final output should really look like.

Could you edit the current results to demonstrate the specific changes that are needed please?
<ORDERS>
  <ORDER>
    <ORDER_NO>121370</ORDER_NO>
    <STATUS>8</STATUS>
    <DEP>AG</DEP>
    <EXWORKS>EXW / EXWORKS Lakewood</EXWORKS>
    <AG_NOTES>WO 121470</AG_NOTES>
    <SCHEDULED_DATE>2013-09-30T00:00:00</SCHEDULED_DATE>
    <DELIV_METH>UPSGround</DELIV_METH>
    <DOC_ALIAS>AG SALES ORDER</DOC_ALIAS>
    <DOC_NO>121370</DOC_NO>
    <ORDER_DATE>2013-09-30T00:00:00</ORDER_DATE>
    <CUST_PO>3454</CUST_PO>
    <SHIPPED>F</SHIPPED>
    <SHIP_CODE>137495</SHIP_CODE>
    <SHIP_NAME>Graphic Automation Ltd</SHIP_NAME>
    <SHIP_ADDRESS>556 Hills Summit</SHIP_ADDRESS>
    <SHIP_CITY>WESTLAKE VILLAGE</SHIP_CITY>
    <SHIP_STATE>CA</SHIP_STATE>
    <SHIP_ZIP>91362</SHIP_ZIP>
    <SHIP_COUNTRY>UNITED STATES</SHIP_COUNTRY>
    <CONTACT_CODE>337495</CONTACT_CODE>
    <CONTACT_NAME>Graphic Automation Ltd</CONTACT_NAME>
    <CONTACT_ADDRESS>556 Hills Summit</CONTACT_ADDRESS>
    <CONTACT_CITY>WESTLAKE VILLAGE</CONTACT_CITY>
    <CONTACT_STATE>CA</CONTACT_STATE>
    <CONTACT_ZIP>91362</CONTACT_ZIP>
    <CONTACT_COUNTRY>UNITED STATES</CONTACT_COUNTRY>
    <ORDER_DETAILS>
      <ITEM_CODE>WD7500BPKT-FR</ITEM_CODE>
      <STATUS>8</STATUS>
      <ITEM_QTY>38</ITEM_QTY>
      <QTY_SHIPPED>0</QTY_SHIPPED>
      <ACTIVE>T</ACTIVE>
      <AUTOSERIAL>T</AUTOSERIAL>
      <ITEMNO>WD7500BPKT-FR</ITEMNO>
      <CATEGORY>ATA3.5</CATEGORY>
      <DESCRIPT>80GB 7200RPM 3.5" 2MB ATA100</DESCRIPT>
      <Q_ON_ORDER>0</Q_ON_ORDER>
      <Q_ON_RESER>500</Q_ON_RESER>
      <QTY_STK>1608</QTY_STK>
    </ORDER_DETAILS>
  </ORDER>
  <ORDER>
    <ORDER_NO>121372</ORDER_NO>
    <STATUS>8</STATUS>
    <DEP>AG</DEP>
    <EXWORKS>EXW / EXWORKS Lakewood</EXWORKS>
    <SCHEDULED_DATE>2013-09-30T00:00:00</SCHEDULED_DATE>
    <DELIV_METH>UPSGround</DELIV_METH>
    <DOC_ALIAS>AG SALES ORDER</DOC_ALIAS>
    <DOC_NO>121372</DOC_NO>
    <ORDER_DATE>2013-09-30T00:00:00</ORDER_DATE>
    <CUST_PO>302780-A</CUST_PO>
    <SHIPPED>F</SHIPPED>
    <SHIP_CODE>136538</SHIP_CODE>
    <SHIP_NAME>Profit Software Tech</SHIP_NAME>
    <SHIP_ADDRESS>9512 Small Crest</SHIP_ADDRESS>
    <SHIP_CITY>MILTON</SHIP_CITY>
    <SHIP_STATE>NH</SHIP_STATE>
    <SHIP_ZIP>3851</SHIP_ZIP>
    <SHIP_COUNTRY>UNITED STATES</SHIP_COUNTRY>
    <CONTACT_CODE>336538</CONTACT_CODE>
    <CONTACT_NAME>Profit Software Tech</CONTACT_NAME>
    <CONTACT_ADDRESS>9512 Small Crest</CONTACT_ADDRESS>
    <CONTACT_CITY>MILTON</CONTACT_CITY>
    <CONTACT_STATE>NH</CONTACT_STATE>
    <CONTACT_ZIP>3851</CONTACT_ZIP>
    <CONTACT_COUNTRY>UNITED STATES</CONTACT_COUNTRY>
    <ORDER_DETAILS>
      <ITEM_CODE>ST31000340NS-FR</ITEM_CODE>
      <STATUS>8</STATUS>
      <ITEM_QTY>5</ITEM_QTY>
      <QTY_SHIPPED>0</QTY_SHIPPED>
      <ACTIVE>T</ACTIVE>
      <AUTOSERIAL>T</AUTOSERIAL>
      <ITEMNO>ST31000340NS-FR</ITEMNO>
      <CATEGORY>SATA2.5</CATEGORY>
      <DESCRIPT>50GB 5400RPM 2.5" 8MB SATA 9.5MM</DESCRIPT>
      <Q_ON_ORDER>0</Q_ON_ORDER>
      <Q_ON_RESER>1222</Q_ON_RESER>
      <QTY_STK>38</QTY_STK>
    </ORDER_DETAILS>
    <ORDER_DETAILS>
      <ITEM_CODE>WD2500AAJS-FR</ITEM_CODE>
      <STATUS>8</STATUS>
      <ITEM_QTY>20</ITEM_QTY>
      <QTY_SHIPPED>0</QTY_SHIPPED>
      <ACTIVE>T</ACTIVE>
      <AUTOSERIAL>T</AUTOSERIAL>
      <ITEMNO>WD2500AAJS-FR</ITEMNO>
      <CATEGORY>SATA</CATEGORY>
      <DESCRIPT>250GB 7200RPM 3.5" 8MB SATA 3GBs</DESCRIPT>
      <Q_ON_ORDER>0</Q_ON_ORDER>
      <Q_ON_RESER>20</Q_ON_RESER>
      <QTY_STK>4373</QTY_STK>
    </ORDER_DETAILS>
  </ORDER>
</ORDERS>

Open in new window

0
rgolenorAuthor Commented:
Thank you Paul and Saurv!  I've made some great progress today with your help.  

Here's where I'm at.. I've made some changes and additions and have the xml output looking great.  The last piece I need to figure out now is how to export this to a file.  

Here's the query at present in case you were curious:



SELECT
        I.ORDER_NO
      , I.ORDER_DATE
      , I.DATE_FLD       AS DELIVERY_DATE
      , I.DEP
      , I.CUSTCHAR9      AS INCOTERM
      , I.CUSTMEMO4      AS AGIL_ORDER_NOTES
      , I.DELIV_METH
      , I.PO             AS BUYER_PO
      , I.INV_AMOUNT     AS ORDERVALUE
      ,    (
            SELECT 
                    S.ADDR_CODE      AS SHIP_CODE
                  , S.NAME           AS SHIP_NAME
                  , S.STREET_ADDRESS AS SHIP_ADDRESS
                  , S.CITY           AS SHIP_CITY
                  , S.STATE          AS SHIP_STATE
                  , S.ZIP            AS SHIP_ZIP
                  , S.COUNTRY        AS SHIP_COUNTRY
                  , s.EMAIL			 AS SHIP_EMAIL
                  , s.TEL1			 AS SHIP_TEL
            FROM ADDRESS S 
            WHERE I.SHIP_CODE = S.ADDR_CODE
            FOR XML PATH ('') , TYPE
            )
      ,    (
            SELECT 
                    B.ADDR_CODE      AS BILL_CODE
                  , B.NAME           AS BILL_NAME
                  , B.STREET_ADDRESS AS BILL_ADDRESS
                  , B.CITY           AS BILL_CITY
                  , B.STATE          AS BILL_STATE
                  , B.ZIP            AS BILL_ZIP
                  , B.COUNTRY        AS BILL_COUNTRY
                  , b.EMAIL          AS BILL_EMAIL
            FROM ADDRESS B 
            WHERE I.BILL_CODE = B.ADDR_CODE and b.ACTIVE = 't'
            FOR XML PATH ('') , TYPE
            )
      ,    (
            SELECT 
                    C.ADDR_CODE      AS CONTACT_CODE
                  , C.NAME           AS CONTACT_NAME
                  , C.STREET_ADDRESS AS CONTACT_ADDRESS
                  , C.CITY           AS CONTACT_CITY
                  , C.STATE          AS CONTACT_STATE
                  , C.ZIP            AS CONTACT_ZIP
                  , C.COUNTRY        AS CONTACT_COUNTRY
                  , C.TEL1           AS CONTACT_TEL
                  , c.EMAIL		     AS CONTACT_EMAIL
            FROM ADDRESS C 
            WHERE I.CONT_CODE = C.ADDR_CODE
            FOR XML PATH ('') , TYPE
            )               
      ,    (
            SELECT
					X.ORDER_NO
                  , X.ITEM_CODE
                  , X.NOTE
                  , X.ITEM_QTY
                  , X.QTY_SHIP       AS QTY_SHIPPED
                  , M.MODEL			 AS MANUFACTURERSKU
                  , M.CATEGORY
                  , M.DESCRIPT
                  , x.ITEM_PRICE     AS UNITPRICE
                  , X.SEQUENCE
            FROM X_INVOIC X
            LEFT JOIN ITEMS M ON X.ITEM_CODE = M.ITEMNO
            WHERE I.ORDER_NO = X.ORDER_NO AND I.STATUS = X.STATUS 
            ORDER BY SEQUENCE ASC
            FOR XML PATH ('SHIPMENTORDERDETAIL') , TYPE
            )
FROM INVOICES I
WHERE I.STATUS = 8
        AND I.DATE_FLD >= DATEADD(DAY, -30, GETDATE())
        AND I.DOC_ALIAS = 'ag SALES ORDER'
        AND I.DEP = 'ag'
        AND I.ORDER_NO IN (SELECT ORDER_NO FROM AG_ORDERS) --<<view I setup to indicate orders need to be pulled
            

FOR XML PATH ('SHIPMENTORDER') , Root('All')

Open in new window

0
PortletPaulfreelancerCommented:
I would suggest:
, Root('ORDERS')
or
, Root('SHIPMENTORDERS')

"All" was just something I used -manually- to help me format the output (here)
0
rgolenorAuthor Commented:
Thanks again Paul and Saurv!  The last piece I need to work out is how to take this and export it to a file.  Any ideas on this?
0
PortletPaulfreelancerCommented:
writing the xml to file.... mmmm

not as simple as "click this" I'm afraid, and there are options.
to answer may require knowing more about what you use and are able to to do, but I'd suggest starting here:

Reading and Writing Files in SQL Server using T-SQL
which proposes a stored procedure "spWriteStringToFile"
code for that here

this requires OLE to be enabled which is documented here:
http://msdn.microsoft.com/en-us/library/ms191188(v=sql.105).aspx

to use it will be something like the following:

DECLARE @xmloutxml xml
DECLARE @xmloutstr nvarchar(max)      

SET @xmloutxml = (
                  /* the xml query here */
                 )
SET @xmloutstr = cast(@xmloutxml as nvarchar(max))
                 
EXEC spWriteStringToFile @xmloutstr,'d:/data/','my_xml.xml'

disclaimer: you probably need someone with more experience in doing this as I don't (it's a long story not relevant here); and you might want to open a new question about this topic "how to write xml to file using tsql?"

If you can use ado.net then saving a query result to file uses: File.WriteAllText()
and there's probably other ways e.g. using C# and so on.

so - I've attempted an answer - but it may not be the best one and I'd prefer you sought a more experienced file writer's answer.

by the way, is this effort required for export? or a web service?
if asking a new question please provide the end objective - it may help determine the best path to follow.
0
rgolenorAuthor Commented:
Thank you for the help Paul, I guess I might have mislead with the question topic as I was thinking of xml as a file output.  I'll research this more and possibly open another question.  Thanks again for all your effort and time.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
XML

From novice to tech pro — start learning today.