XML to SQL Server Parsing Issue

Question

I'm parsing below XML and trying to fetch all the attributes/values of node.

declare @XBL xml='
<Root>
      <Department>
             <Employees>
                <Employee type="temp">
                 Jason
                </Employee>
                <Employee type="perm">
                 Roy
                </Employee>
             </Employees>
      </Department>
      <Department>
             <Employees >
                <Employee type="temp2">
                 Kevin
                </Employee>
             </Employees>
      </Department>
</Root>'

SELECT  
    [Type] = XC.value('(@type)[1]', 'varchar(25)'),
       [Name] = XC.value('(../Employee)[1]' , 'varchar(30)')
FROM
    @XBL.nodes('Root/Department/Employees/Employee') AS XTbl(XC)

Output of above query gives me all the attributes but with first value only(Jason).

Type  Name 
temp  Jason
perm  Jason
temp2 Kevin

Expected Output:

Type  Name 
temp  Jason
perm  Roy
temp2 Kevin

Thom A · Accepted Answer · 2018-07-04 14:04:36Z

2

This should be what you're after:

SELECT XBL.E.value('@type','varchar(25)') AS [Type],
       XBL.E.value('(./text())[1]','varchar(30)') AS [Name]
FROM @XBL.nodes('Root/Department/Employees/Employee') XBL(E);

Note the use of /text() as well. When returning data from inside a node, adding /text() actually improves the performance of the query.

Edit: Also, based on your sample xml, the value returned for [Name] would actually be '{Line break} Jason{Line break}' (Obviously replace the line break with the actual character). Is that what you intend, or do you want the whitespace and line breaks/carriage returns removed as well?

answered Jul 4, 2018 at 14:04

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

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

Comments

Andomar · Accepted Answer · 2018-07-04 14:04:18Z

1

You're selecting the first Employee child of the parent Department:

[Name] = XC.value('(../Employee)[1]' , 'varchar(30)'
                   ^^^^^^^^^^^^^^^^

To select the current Employee, use:

[Name] = XC.value('(.)[1]' , 'varchar(30)')
                   ^^^^^^

Example at SQL Fiddle.

answered Jul 4, 2018 at 14:04

Andomar

239k55 gold badges387 silver badges412 bronze badges

Collectives™ on Stack Overflow

XML to SQL Server Parsing Issue

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related