SQL Query / Xml

Question

I'm trying to query a table which has a column that contains straight xml data. Within the query I'm querying columns that hold straight data (int, vchar etc) but I'm also querying the xml column. In the xml column i want to grab a value within the xml and return null if it doesn't exist. I have the following query that almost works but returns duplicates. Need help!

I have my root xml CodeFiveReport then within it Properties and within that Property which has a serial number. I'm trying to grab the serial number if it exists and displaying it.

select Distinct rs.Id
        , rs.CaseNumber
        , rs.StartDate
        , rs.[Status]
        , rs.PatrolDistrict
        , rs.PrimaryUnit
        , rs.Location
        , rs.ReportType
        , rs.IncidentType
        , rs.UserId
        , rs.UnitId
        , rs.UnitCode
        , rs.IsLocked
        , rs.LockedBy
        , rs.AgencyId
        , rl.ReportName
        , rl.ParentId
        , TempTable.Party.value('(SerialNumber/text())[1]', 'varchar(50)') as SerialNumber
    from dbo.vw_ReportSummary rs OUTER APPLY Report.nodes('/CodeFiveReport/Properties/Property') AS TempTable(Party)
    left outer join dbo.ReportLookup rl on rs.Id = rl.Id
    where  rs.[Status] = 'Approved'  
    order by rs.Id

Answer 1

Well, I was able to solve the problem

I changed Report.nodes('/CodeFiveReport/Properties/Property') to Report.nodes('/CodeFiveReport/Properties')

In turn I also changed my TempTable query to: TempTable.Party.value('(Property/SerialNumber/text())[1]', 'varchar(50)') as SerialNumber and that seemed to fix the duplicates.

Thanks for your help everybody.

Answer 2

Hard to say without knowing your exact database schema. Assuming that this is T-SQL: Have a look at CTE (common table expressions) and split your statement in two steps. That makes these kind of statements usually much simpler and often more efficient.