• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 234
  • Last Modified:

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

0
rgolenor
Asked:
rgolenor
  • 5
  • 2
1 Solution
 
jorge_torizResearch & Development ManagerCommented:
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

0
 
rgolenorAuthor Commented:
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.
0
 
jorge_torizResearch & Development ManagerCommented:
Can you post your desired result?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
rgolenorAuthor Commented:
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

0
 
rgolenorAuthor Commented:
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

0
 
rgolenorAuthor Commented:
I figured it out.. this is what I needed:

DECLARE @Header XML
DECLARE @Body   XML

SET @Header =
	( 
	SELECT
	I.ORDER_NO		 AS MessageID  
      , I.ORDER_DATE	 AS Date
      , A.MessageType --: ShipmentOrder
      , A.Action --: Input
		,   (    
			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 
) 
Set @Body =
(
 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') , TYPE ) 

SELECT @HEADER
UNION ALL
SELECT @BODY
FOR XML PATH (''), ROOT ('Messsage'), TYPE
;

Open in new window

0
 
rgolenorAuthor Commented:
I figured it out and the formatting worked as needed.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now