CODEWITHSUNDEEP
sql-serverxmlxquery
user8820770
user8820770

Reputation: 13

XML returning too many rows

From the below query I am returning 7 rows when I expect to only get two. What am I doing wrong?

 go
    declare @xml xml =
    '<people>
      <person ID="18" DOB="03 Apr 1955">
        <name>
          <FirstName>Walter</FirstName>
          <LastName>Thrip</LastName>
        </name>
        <Sex>M</Sex>
         <Sex>X</Sex>
        <title>Sir</title>
      </person>
      <person ID="19" DOB="01 Jan 2010">
        <name>
          <FirstName>john</FirstName>
          <LastName>smith</LastName>
        </name>
        <Sex>m</Sex>
        <DOD>20 Jan 2014</DOD>
      </person>
    </people>'
    select  
          n.value('../@ID','int') as ID
          ,n.value('@DOB','datetime') as DOB
          ,n.value('FirstName[1]','varchar(20)') as FirstName
          ,n.value('LastName[1]','varchar(20)') as LastName
          ,n.value('../Sex[1]', 'char(1)') as Sex
          ,n.value('../DOD[1]', 'datetime') as DOD
          ,n.value('../Title[1]', 'varchar(10)') as Title
    from @xml.nodes('(/people/person/*)') as a(n)

Upvotes: 1

Views: 78

Answers (1)

har07
har07

Reputation: 89285

That's because you shred the XML on child of person level and there are 7 even them. You should shred on person element instead :

select  
      n.value('@ID','int') as ID
      ,n.value('@DOB','datetime') as DOB
      ,n.value('(name/FirstName)[1]','varchar(20)') as FirstName
      ,n.value('(name/LastName)[1]','varchar(20)') as LastName
      ,n.value('Sex[1]', 'char(1)') as Sex
      ,n.value('DOD[1]', 'datetime') as DOD
      ,n.value('Title[1]', 'varchar(10)') as Title
from @xml.nodes('/people/person') as a(n)

rextester demo

Upvotes: 3

Related Questions