Need help to format output of SQL Server XML sibling query

Question

Consider the following SQL Server XML output:

<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!

Gottfried Lesigang · Accepted Answer · 2016-01-18 11:13:27Z

1

The best (best in usage and performance!) is FOR XML PATH. It is very intuitive and easy to define any output you want simply by naming them.

SELECT 
    'TEST_CUSTOMER_01' AS [CUST_ID]
    ,'MY_ORG' AS [ORG_CODE]
    ,'SHIP_TO' AS [CUSTOMER_TYPE/CUST_TYPE]
    ,'US' AS [CUSTOMER_COUNTRY/CTRY_CODE]
--FROM   
--    CUSTOMER
--WHERE 
--    CUSTOMER.CUST_ID = 'TEST_CUSTOMER_01'
FOR XML PATH('CUSTOMER')

answered Jan 18, 2016 at 11:13

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shiju Shaji Over a year ago

Wow.. a much better answer than my answer.:)

HDW Over a year ago

Thanks for a great answer!

Shiju Shaji · Accepted Answer · 2016-01-18 04:58:42Z

Try this query.

select CUST_ID,ORG_CODE,( SELECT CUSTOMER_TYPE.CUST_TYPE AS CUST_TYPE
  FROM   @customer_type CUSTOMER_TYPE
  WHERE  CUSTOMER.CUST_ID = CUSTOMER_TYPE.CUSTOMER_ID
  FOR XML  path (''),TYPE) AS 'CUSTOMER_TYPE' ,(
SELECT CUSTOMER_COUNTRY.CTRY_CODE as CTRY_CODE
  FROM   @customer_country CUSTOMER_COUNTRY
  WHERE  CUSTOMER.CUST_ID = CUSTOMER_COUNTRY.CUSTOMER_ID
 for xml path (''),TYPE
) AS 'CUSTOMER_COUNTRY'   from
@customer CUSTOMER
for xml auto, ELEMENTS

Using the above query you will get the result as

<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>

Collectives™ on Stack Overflow

Need help to format output of SQL Server XML sibling query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related