getting elements values from xml data

Question

I have some xmls with different structures from which I want to get all the values including the nulls. After some time I got to write this little sample piece of code:

declare @xml xml = '
<e xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Code>code1</Code>
  <DepartmentCode xsi:nil="true" />
  <Email>email1</Email>
  <AddressId xsi:nil="true" />
  <IsActive>1</IsActive>
</e>
<e xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Code>code2</Code>
  <DepartmentCode xsi:nil="true" />
  <Email>email2</Email>
  <AddressId xsi:nil="true" />
  <IsActive>0</IsActive>
</e>'

;with sub as 
(
SELECT Tbl.Col.value('.', 'nvarchar(max)') as Value
FROM @xml.nodes('e//text()') Tbl(Col)
)
select * from sub s

But it doesn't include nulls. How can change the code to get the nulls in result?

podiluska · Accepted Answer · 2014-05-21 08:28:17Z

3

You're not getting them because you're selecting from //text()

Try selecting from

SELECT  Tbl.Col.value('.', 'nvarchar(max)') as Value
FROM @xml.nodes('e//*') Tbl(Col)

answered May 21, 2014 at 8:28

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

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

1 Comment

Eralper Over a year ago

I guess you mean following: SELECT NULLIF( Tbl.Col.value('.', 'nvarchar(max)') , '')as Value FROM @xml.nodes('e//*') Tbl(Col)

Collectives™ on Stack Overflow

getting elements values from xml data

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related