Consider the following Sql Server XML output below.
<CUSTOMER> <CUST_ID>TEST_CUSTOMER_01</CUST_ID> <ORG_CODE>MY_ORG</ORG_CODE> <CUSTOMER_TYPE CUST_TYPE="RETAIL" /> <CUSTOMER_COUNTRY CTRY_CODE="US" /> </CUSTOMER> It was generated by the following Sql Statement.
SELECT CUSTOMER.CUST_ID, CUSTOMER.ORG_CODE, ( SELECT CUSTOMER_TYPE.CUST_TYPE FROM CUSTOMER_TYPE WHERE CUSTOMER.CUST_ID = CUSTOMER_TYPE.CUSTOMER_ID FOR XML AUTO, TYPE ), ( SELECT CUSTOMER_COUNTRY.CTRY_CODE FROM CUSTOMER_COUNTRY WHERE CUSTOMER.CUST_ID = CUSTOMER_COUNTRY.CUSTOMER_ID FOR XML AUTO, TYPE ) FROM CUSTOMER WHERE CUSTOMER.CUST_ID = 'TEST_CUSTOMER_01' FOR XML AUTO, ELEMENTS GO It's required that the output look like the output below. Substituting "ELEMENTS" for the two "TYPE" words in the query above doesn't do it. How then do I do it?
<CUSTOMER> <CUST_ID>TEST_CUSTOMER_01</CUST_ID> <ORG_CODE>MY_ORG</ORG_CODE> <CUSTOMER_TYPE> <CUST_TYPE>SHIP_TO</CUST_TYPE> </CUSTOMER_TYPE> <CUSTOMER_COUNTRY> <CTRY_CODE>US</CTRY_CODE> </CUSTOMER_COUNTRY> </CUSTOMER> Thanks!
No comments:
Post a Comment