SQL Server - For XML Path (embed element)

Question

INPUT:

SELECT EmpName AS [Name],
       EmpDOB       AS [Birthdate],
       EmpSalary        AS [WageAmount],
    Add1    AS [Address1],
    Add2    AS [Address2],
    Mobile  AS [Mobile]
FROM   Employee
FOR XML PATH

OUTPUT: (which works fine)

<Employee>
<Name>Conrad</Name>
<Birthdate>14-oct-76</BirthDate>
<WageAmount>10000</WageAmount>
<AdditionalInfo>
    <Address1>Washington DC</Address1>
    <Address2>DC</Address2>
    <Mobile>989898989</Mobile>
</AdditionalInfo>
</Employee>

I'm able to generate output - but i want this element to be added below mobile tag (CONTACT TYPE element - how can we add this)

<Mobile>989898989</Mobile>
**<ContactType><mobilephone/></ContactType>**

gofr1 · Accepted Answer · 2016-06-10 11:17:58Z

1

;WITH Employee AS (
SELECT  'Conrad' as EmpName,
        '14-oct-76' as EmpDOB,
        10000 as EmpSalary,
        'Washington DC' as Add1,
        'DC' as Add2,
        989898989 as Mobile
)

SELECT EmpName AS [Name],
       EmpDOB       AS [Birthdate],
       EmpSalary        AS [WageAmount],
    Add1    AS [AdditionalInfo/Address1],
    Add2    AS [AdditionalInfo/Address2],
    Mobile  AS [AdditionalInfo/ContactType/Mobile]
FROM   Employee
FOR XML PATH ('Employee')

Output:

<Employee>
  <Name>Conrad</Name>
  <Birthdate>14-oct-76</Birthdate>
  <WageAmount>10000</WageAmount>
  <AdditionalInfo>
    <Address1>Washington DC</Address1>
    <Address2>DC</Address2>
    <ContactType>
      <Mobile>989898989</Mobile>
    </ContactType>
  </AdditionalInfo>
</Employee>

edited Jun 10, 2016 at 11:17

answered Jun 10, 2016 at 9:51

gofr1

16k11 gold badges47 silver badges56 bronze badges

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

Collectives™ on Stack Overflow

SQL Server - For XML Path (embed element)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related