XML : Need help to format output of Sql Server XML sibling query

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