XML format in SQL Server

Question

I would like to seek help regarding format with XML:

<TestFiles>
  <Tests>
    <Emp>
      <FName>Good</FName>
      <LName>Boys</LName>
      <Traits>
        <Trait> 
          <Trait1></Trait1>
          <TraitDesc></TraitDesc>
        <Trait>
        <Trait>
          <Trait2></Trait2>
          <TraitDesc></TraitDesc>
        <Trait>   
        <Trait>
          <Trait3></Trait3>
          <TraitDesc></TraitDesc>
        <Trait>   
      </Traits>  
      <Phone>897-122-222</Phone>
   </Emp>
  </Tests>
</TestFiles>

I have trouble in formatting my select statement. Any Suggestion or Help is really appreciated.

Show what you have tried so far, we don't even know what you are trying to select. — JK.
– JK., Commented May 19, 2011 at 6:48

marc_s · Accepted Answer · 2011-05-19 07:00:06Z

2

With a query like this:

SELECT  
    dbo.emp.FName ,
    dbo.emp.LName ,
    (SELECT 
        ID AS '@ID',
        TraitDesc 
     FROM dbo.Trait t
     WHERE t.EmpID = emp.ID
     FOR XML PATH('Trait'), TYPE) AS 'Traits',
    dbo.emp.Phone
FROM
    emp
FOR XML PATH('Emp'), ROOT('Tests')

you can get an output like this:

<Tests>
   <Emp>
      <FName>Good</FName>
      <LName>Boys</LName>
      <Traits>
         <Trait ID="1">
            <TraitDesc>Trait #1</TraitDesc>
         </Trait>
         <Trait ID="2">
            <TraitDesc>Trait #2</TraitDesc>
         </Trait>
         <Trait ID="3">
            <TraitDesc>Trait #3</TraitDesc>
         </Trait>
      </Traits>
      <Phone>897-122-222</Phone>
   </Emp>
</Tests>

What you probably cannot do (or at least not without dirty hacks and messy code) is having different <Trait1>, <Trait2>, <Trait3> XML tags for each child node.

edited May 19, 2011 at 7:00

answered May 19, 2011 at 6:52

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

Collectives™ on Stack Overflow

XML format in SQL Server

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