Solved

Need to format xml with specific tags

Posted on 2013-12-05
7
219 Views
Last Modified: 2013-12-31
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
Comment
Question by:rgolenor
  • 5
  • 2
7 Comments
 
LVL 15

Expert Comment

by:jorge_toriz
ID: 39699690
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
 

Author Comment

by:rgolenor
ID: 39728036
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
 
LVL 15

Expert Comment

by:jorge_toriz
ID: 39728137
Can you post your desired result?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:rgolenor
ID: 39728187
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
 

Author Comment

by:rgolenor
ID: 39733037
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
 

Accepted Solution

by:
rgolenor earned 0 total points
ID: 39740368
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
 

Author Closing Comment

by:rgolenor
ID: 39747878
I figured it out and the formatting worked as needed.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question