CODEWITHSUNDEEP
sql-servert-sqlxquery
lkurylo
lkurylo

Reputation: 1651

getting elements values from xml data

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?

Upvotes: 0

Views: 43

Answers (1)

podiluska
podiluska

Reputation: 51514

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)

Upvotes: 3

Related Questions