asked on

Need to format xml with specific tags

I need to figure out how to place the data from this query into specific tags. The tags being : (Message, Head, ShipmentOrderHeader, ShipmentOrderDetail) I've commented in where I would need the tags to start and end but I'm not sure how to implement them.

SET @XMLOutput = (
-->>Start MESSAGE<<--
-->>Start HEAD segment<<
SELECT
		I.ORDER_NO		 AS MessageId  
      , I.ORDER_DATE	 AS Date
      , A.MessageType --: Receipt 
      , A.Action--: Input
      , A.Sender--: 
      , A.SystemID--: WMS 
	  , A.CompanyID--: AG
      , I.ORDER_DATE
      , I.DATE_FLD       AS DELIVERYDATE      
      ,    (
-->>End HEAD Segment<<--
-->>Start SHIPMENTORDERHEADER Segment<<--           
            SELECT 
                    --S.ADDR_CODE      AS SHIP_CODE
                    C.NAME           AS C_COMPANY
                  , C.STREET_ADDRESS AS C_ADDRESS1
                  , C.CITY           AS C_CITY
                  , C.STATE          AS C_STATE
                  , C.ZIP            AS C_ZIP
                  , C.COUNTRY        AS C_COUNTRY
                  , C.TEL1			 AS C_PHONE1
            FROM ADDRESS C 
              WHERE I.SHIP_CODE = C.ADDR_CODE
            FOR XML PATH ('') , TYPE
            )
      ,    (
            SELECT 
					I.PO             AS BUYERPO
                  --, B.ADDR_CODE      AS BILLTOKEY
                  , B.NAME           AS B_COMPANY
                  , B.STREET_ADDRESS AS B_ADDRESS1
                  , B.CITY           AS B_CITY
                  , B.STATE          AS B_STATE
                  , B.ZIP            AS B_ZIP
                  , B.COUNTRY        AS B_COUNTRY
                  , B.TEL1           AS B_PHONE1
				  , I.CUSTCHAR9      AS INCOTERM
				  , A.TYPE	         AS TYPE
				  , I.CUSTMEMO4      AS NOTES
				  , C.EMAIL			 AS C_EMAIL1
				  , I.DELIV_METH     AS TRANSPORTATIONSERVICE
                --, I.INV_AMOUNT     AS ORDERVALUE
            FROM ADDRESS B, ADDRESS C, AGILITY_FIXEDVALUES A 
              WHERE I.BILL_CODE = B.ADDR_CODE AND B.ACTIVE = 'T' and I.SHIP_CODE = C.ADDR_CODE
            FOR XML PATH ('') , TYPE
            )
      ,    (
            SELECT 
                    X.NAME           AS SUSR1
                  , X.FULL_ADDRESS   AS SUSR2
                  , X.TEL1           AS SUSR3
                  , X.EMAIL		     AS SUSR4
            FROM ADDRESS X
              WHERE I.CONT_CODE = X.ADDR_CODE
            FOR XML PATH ('') , TYPE
            ) 
-->>End SHIPMENTORDERHEADER<<--
-->>Start SHIPMENTORDERDETAIL<<--              
      ,    (
            SELECT
		    I.ORDER_NO       AS EXTERNORDERKEY
		  , X.SEQUENCE       AS EXTERNLINENO
                  , X.ITEM_CODE      AS SKU
                  , M.MODEL			 AS MANUFACTURERSKU
                  , X.ITEM_QTY - x.QTY_SHIP       AS OPENQTY
                  , A.UOM            
                  , X.ITEM_PRICE     AS UNITPRICE                
                  , X.NOTE           AS NOTES
            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, AGILITY_FIXEDVALUES A 
WHERE I.STATUS = 8
        AND I.DOC_ALIAS LIKE '%AG%'
        AND I.SHIPPED = 'F'
       --AND I.ORDER_NO IN (SELECT DOC_NO FROM AG_ORDERS_PROCESSING)
-->>End SHIPMENTORDERDETAIL<<--
-->>End MESSAGE<<--            
FOR XML PATH ('Head') , ROOT('Message'))
SELECT @XmlOutput

Open in new window

jorge_toriz

Using AdventureWorks, this could be an approach:

SELECT (
	SELECT SalesOrderID, RevisionNumber, OrderDate
	FROM Sales.SalesOrderHeader
	WHERE SalesOrderID = 43659
	FOR XML RAW('Order'), ROOT('HEAD'), TYPE
), (
	SELECT SOD.SalesOrderDetailID, SOD.OrderQty, SOD.LineTotal, P.Name
	FROM Sales.SalesOrderDetail SOD
	INNER JOIN Production.Product P
	ON SOD.ProductID = P.ProductID
	WHERE SOD.SalesOrderID = 43659
	FOR XML RAW('Product'), ROOT('DETAILS'), TYPE
)
FOR XML RAW('MESSAGE')

Open in new window

Result:

<MESSAGE>
  <HEAD>
    <Order SalesOrderID="43659" RevisionNumber="1" OrderDate="2001-07-01T00:00:00" />
  </HEAD>
  <DETAILS>
    <Product SalesOrderDetailID="1" OrderQty="1" LineTotal="2024.994000" Name="Mountain-100 Black, 42" />
    <Product SalesOrderDetailID="2" OrderQty="3" LineTotal="6074.982000" Name="Mountain-100 Black, 44" />
    <Product SalesOrderDetailID="3" OrderQty="1" LineTotal="2024.994000" Name="Mountain-100 Black, 48" />
    <Product SalesOrderDetailID="4" OrderQty="1" LineTotal="2039.994000" Name="Mountain-100 Silver, 38" />
    <Product SalesOrderDetailID="5" OrderQty="1" LineTotal="2039.994000" Name="Mountain-100 Silver, 42" />
    <Product SalesOrderDetailID="6" OrderQty="2" LineTotal="4079.988000" Name="Mountain-100 Silver, 44" />
    <Product SalesOrderDetailID="7" OrderQty="1" LineTotal="2039.994000" Name="Mountain-100 Silver, 48" />
    <Product SalesOrderDetailID="8" OrderQty="3" LineTotal="86.521200" Name="Long-Sleeve Logo Jersey, M" />
    <Product SalesOrderDetailID="9" OrderQty="1" LineTotal="28.840400" Name="Long-Sleeve Logo Jersey, XL" />
    <Product SalesOrderDetailID="10" OrderQty="6" LineTotal="34.200000" Name="Mountain Bike Socks, M" />
    <Product SalesOrderDetailID="11" OrderQty="2" LineTotal="10.373000" Name="AWC Logo Cap" />
    <Product SalesOrderDetailID="12" OrderQty="4" LineTotal="80.746000" Name="Sport-100 Helmet, Blue" />
  </DETAILS>
</MESSAGE>

Open in new window

rgolenor

ASKER

I've tried the suggestion above with no success. It's likely just me. I'm getting close to figuring it out so I'm going to post what I have right now that still needs a little work:

DECLARE @XMLOutput XML 

SET @XMLOutput = (

SELECT
	I.ORDER_NO	 AS MessageID  
      , I.ORDER_DATE	 AS Date
      , A.MessageType 
      , A.Action 
		,   (    
			SELECT 
				  A.SystemID
				, A.CompanyID
			FROM AG_FIXEDVALUES A  
			FOR XML PATH ('Recipient'), type
			)			
			,   (    
			SELECT 
				  I.ORDER_NO as ShipmentOrder
			FOR XML PATH ('Body') , type
			)					
       , (
          SELECT 
	            I.ORDER_DATE
		  , I.DATE_FLD       AS DELIVERYDATE      
                  , C.NAME           AS C_COMPANY
                  , C.STREET_ADDRESS AS C_ADDRESS1
                  , C.CITY           AS C_CITY
                  , C.STATE          AS C_STATE
                  , C.ZIP            AS C_ZIP
                  , C.CUSTCHAR1      AS C_COUNTRY
                  , C.TEL1			 AS C_PHONE1
                  , I.CUST_CODE      AS C_FAX2 
		  , I.PO             AS BUYERPO
                  , B.NAME           AS B_COMPANY
                  , B.STREET_ADDRESS AS B_ADDRESS1
                  , B.CITY           AS B_CITY
                  , B.STATE          AS B_STATE
                  , B.ZIP            AS B_ZIP
                  , B.CUSTCHAR1      AS B_COUNTRY
                  , B.TEL1           AS B_PHONE1
				  , SUBSTRING (I.CUSTCHAR9 ,1 ,3) AS INCOTERM				  
				  , A.TYPE	         AS TYPE
				  , C.EMAIL			 AS C_EMAIL1
				  , I.DELIV_METH     AS TRANSPORTATIONSERVICE
				  , I.SALES_REP      AS SUSR2
				  , I.TERMS			 AS TRAILEROWNER
	                          , I.CUSTMEMO4      AS NOTES2 				  
            FROM ADDRESS B, ADDRESS C, AG_FIXEDVALUES A--, ADDRESS D
              WHERE I.BILL_CODE = B.ADDR_CODE 
              and I.SHIP_CODE = C.ADDR_CODE
           FOR XML PATH ('ShipmentOrderHeader') , type
                 )               
      ,    (
            SELECT
	            I.ORDER_NO       AS EXTERNORDERKEY
		  , X.SEQUENCE       AS EXTERNLINENO
                  , X.ITEM_CODE      AS SKU
                  , M.MODEL			 AS MANUFACTURERSKU
                  , X.ITEM_QTY - x.QTY_SHIP       AS OPENQTY
                  , A.UOM            
                  , X.ITEM_PRICE     AS UNITPRICE                
            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
              and M.ITEMNO is not null
            ORDER BY SEQUENCE ASC
            FOR XML PATH ('ShipmentOrderDetail') , TYPE
            )
FROM INVOICES I, AG_FIXEDVALUES A 
WHERE I.STATUS = 8
        AND I.DOC_ALIAS LIKE '%AG%'        
        AND I.SHIPPED = 'F'     
        AND I.ORDER_NO IN (SELECT DOC_NO FROM AG_ORDERS_PROCESSING)   
FOR XML PATH ('Head') , ROOT('Message'))

SELECT @XmlOutput
;

Open in new window

The problem I'm still having is that I need the path (tags) in specific places.
For example I need the Head Tag closed after Recipient and I need the Body tag closed after ShipmentOrderDetail. I keep tinkering but without much luck.

jorge_toriz

Can you post your desired result?

rgolenor

ASKER

I believe this would be the desired result:

<Message>
  <Head>
    <MessageID>121929</MessageID>
    <Date>2013-12-06T00:00:00</Date>
    <MessageType>ShipmentOrder</MessageType>
    <Action>Input</Action>
    <Recipient>
      <SystemID>WMS 3.9</SystemID>
      <CompanyID>AG</CompanyID>
    </Recipient>
  </Head>
    <Body>
      <ShipmentOrder>121929</ShipmentOrder>
    <ShipmentOrderHeader>
      <ORDER_DATE>2013-12-06T00:00:00</ORDER_DATE>
      <DELIVERYDATE>2013-12-06T00:00:00</DELIVERYDATE>
      <C_COMPANY>dfg co.</C_COMPANY>
      <C_ADDRESS1>4 Steet Ave </C_ADDRESS1>
      <C_CITY>MEMPHIS</C_CITY>
      <C_STATE>TN</C_STATE>
      <C_ZIP>38118</C_ZIP>
      <C_COUNTRY>USA</C_COUNTRY>
      <C_PHONE1>9016144170</C_PHONE1>
      <C_FAX2>60832</C_FAX2>
      <BUYERPO>28999</BUYERPO>
      <B_COMPANY>dfg co.</B_COMPANY>
      <B_ADDRESS1>154039 E. abd Ave.</B_ADDRESS1>
      <B_CITY>CITY OF INDUSTRY</B_CITY>
      <B_STATE>CA</B_STATE>
      <B_ZIP>91745</B_ZIP>
      <B_COUNTRY>USA</B_COUNTRY>
      <B_PHONE1>6265540700</B_PHONE1>
      <INCOTERM>EXW</INCOTERM>
      <TYPE>0</TYPE>
      <C_EMAIL1 />
      <TRANSPORTATIONSERVICE>CourierPickup</TRANSPORTATIONSERVICE>
      <SUSR2>Greg</SUSR2>
      <TRAILEROWNER>N60</TRAILEROWNER>
      <NOTES2>LANDED PER CONTRACT</NOTES2>
    </ShipmentOrderHeader>
    <ShipmentOrderDetail>
      <EXTERNORDERKEY>121929</EXTERNORDERKEY>
      <EXTERNLINENO>1</EXTERNLINENO>
      <SKU>SA31524AS-FR</SKU>
      <MANUFACTURERSKU>SA31524AS</MANUFACTURERSKU>
      <OPENQTY>0.000000</OPENQTY>
      <UOM>EA</UOM>
      <UNITPRICE>42.500000</UNITPRICE>
    </ShipmentOrderDetail>
    <ShipmentOrderDetail>
      <EXTERNORDERKEY>121929</EXTERNORDERKEY>
      <EXTERNLINENO>3</EXTERNLINENO>
      <SKU>WCH6700ABSL-K</SKU>
      <MANUFACTURERSKU>WCH6700ABSL</MANUFACTURERSKU>
      <OPENQTY>200.000000</OPENQTY>
      <UOM>EA</UOM>
      <UNITPRICE>38.000000</UNITPRICE>
    </ShipmentOrderDetail>
    <ShipmentOrderDetail>
      <EXTERNORDERKEY>121929</EXTERNORDERKEY>
      <EXTERNLINENO>5</EXTERNLINENO>
      <SKU>WJBH56500BK-K</SKU>
      <MANUFACTURERSKU>WJBH56500BK</MANUFACTURERSKU>
      <OPENQTY>100.000000</OPENQTY>
      <UOM>EA</UOM>
      <UNITPRICE>39.850</UNITPRICE>
    </ShipmentOrderDetail>
    <ShipmentOrderDetail>
      <EXTERNORDERKEY>121929</EXTERNORDERKEY>
      <EXTERNLINENO>7</EXTERNLINENO>
      <SKU>W0J12283</SKU>
      <MANUFACTURERSKU>01f2a283</MANUFACTURERSKU>
      <OPENQTY>0.000000</OPENQTY>
      <UOM>EA</UOM>
      <UNITPRICE>35.65</UNITPRICE>
    </ShipmentOrderDetail>
    </Body>
</Message>

Open in new window

rgolenor

ASKER

I think this might be close to what I need .. just getting one error that maybe someone can help workout.

The error is:
Msg 116, Level 16, State 1, Line 98
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

DECLARE @XMLOutput XML 

SET @XMLOutput = (
SELECT 
	( 
	SELECT
	I.ORDER_NO		 AS MessageID  
      , I.ORDER_DATE	 	 AS Date
      , A.MessageType
      , A.Action
		,   (    
			SELECT 
				  A.SystemID
				, A.CompanyID
			FROM AG_FIXEDVALUES A  
			FOR XML PATH ('Recipient') , type) 
FROM INVOICES I, AG_FIXEDVALUES A 
WHERE I.STATUS = 8
        AND I.DOC_ALIAS LIKE '%AG%'        
        AND I.SHIPPED = 'F'
        AND I.ORDER_NO IN (SELECT DOC_NO FROM AG_ORDERS_PROCESSING)
FOR XML PATH ('Head') , type 
)
,
(
 SELECT 
	  I.ORDER_NO as ShipmentOrder					 					
       , (
          SELECT 
		    I.ORDER_DATE
		  , I.DATE_FLD       AS DELIVERYDATE      
                  , C.NAME           AS C_COMPANY
                  , C.STREET_ADDRESS AS C_ADDRESS1
                  , C.CITY           AS C_CITY
                  , C.STATE          AS C_STATE
                  , C.ZIP            AS C_ZIP
                  , C.CUSTCHAR1      AS C_COUNTRY
                  , C.TEL1	     AS C_PHONE1
                  , i.CUST_CODE      AS C_FAX2 
		  , I.PO             AS BUYERPO
                  , B.NAME           AS B_COMPANY
                  , B.STREET_ADDRESS AS B_ADDRESS1
                  , B.CITY           AS B_CITY
                  , B.STATE          AS B_STATE
                  , B.ZIP            AS B_ZIP
                  , B.CUSTCHAR1      AS B_COUNTRY
                  , B.TEL1           AS B_PHONE1
		  , SUBSTRING (I.CUSTCHAR9 ,1 ,3) AS INCOTERM				  
		  , A.TYPE	     AS TYPE
		  , C.EMAIL          AS C_EMAIL1
		  , I.DELIV_METH     AS TRANSPORTATIONSERVICE
		  , I.SALES_REP      AS SUSR2
		  , I.TERMS	     AS TRAILEROWNER                
	          , I.CUSTMEMO4      AS NOTES2 				  
            FROM ADDRESS B, ADDRESS C, AG_FIXEDVALUES A
              WHERE I.BILL_CODE = B.ADDR_CODE 
              AND I.SHIP_CODE = C.ADDR_CODE
           FOR XML PATH ('ShipmentOrderHeader') , type
                 )               
      ,    (
            SELECT
		    I.ORDER_NO			AS EXTERNORDERKEY
		  , X.SEQUENCE			AS EXTERNLINENO
                  , X.ITEM_CODE			AS SKU
		  , M.MODEL			AS MANUFACTURERSKU
                  , X.ITEM_QTY - x.QTY_SHIP     AS OPENQTY
                  , A.UOM            
                  , X.ITEM_PRICE		AS UNITPRICE                
                  , ('WARRANTY: ' + UPPER (X.CUSTCHAR1)) AS NOTES
            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
              AND M.ITEMNO IS NOT NULL
            ORDER BY SEQUENCE ASC
            FOR XML PATH ('ShipmentOrderDetail') , TYPE
            )
FROM INVOICES I, AG_FIXEDVALUES A 
WHERE I.STATUS = 8
        AND I.DOC_ALIAS LIKE '%AG%'        
        AND I.SHIPPED = 'F'
        AND I.ORDER_NO IN (SELECT DOC_NO FROM AG_ORDERS_PROCESSING)        
FOR XML PATH ('Body') , ROOT('Message') )  )

SELECT @XmlOutput

Open in new window

ASKER CERTIFIED SOLUTION

rgolenor

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rgolenor

ASKER

I figured it out and the formatting worked as needed.