Given the following XML, how would I return the BranchID, PeriodType, StartDate and EndDate all in the same query:
I have the following, and I want to add the id attribute from the Branch element:
SELECT
x.value('@pn[1]', 'tinyint') AS PeriodNumber,
x.value('StartDate[1]','datetime') AS StartDate,
x.value('EndDate[1]','datetime') AS EndDate
FROM @dates.nodes('/root/Branch/PeriodType[@pt="0"]/PeriodNumber') d(x)
DECLARE @dates xml, @startdate datetime
SET @dates = '<root>
<Branch id="1">
<PeriodType pt="0">
<PeriodNumber pn="1">
<StartDate>06/23/2008</StartDate>
<EndDate>06/24/2008</EndDate>
</PeriodNumber>
<PeriodNumber pn="2">
<StartDate>06/16/2008</StartDate>
<EndDate>06/22/2008</EndDate>
</PeriodNumber>
<PeriodNumber pn="3">
<StartDate>06/09/2008</StartDate>
<EndDate>06/15/2008</EndDate>
</PeriodNumber>
<PeriodNumber pn="4">
<StartDate>06/02/2008</StartDate>
<EndDate>06/08/2008</EndDate>
</PeriodNumber>
</PeriodType>
<PeriodType pt="1">
<PeriodNumber pn="1">
<StartDate>06/01/2008</StartDate>
<EndDate>06/23/2008</EndDate>
</PeriodNumber>
<PeriodNumber pn="2">
<StartDate>05/01/2008</StartDate>
<EndDate>05/31/2008</EndDate>
</PeriodNumber>
<PeriodNumber pn="3">
<StartDate>04/01/2008</StartDate>
<EndDate>04/30/2008</EndDate>
</PeriodNumber>
<PeriodNumber pn="4">
<StartDate>03/01/2008</StartDate>
<EndDate>03/31/2008</EndDate>
</PeriodNumber>
</PeriodType>
<PeriodType pt="2">
<PeriodNumber pn="1">
<StartDate>04/01/2008</StartDate>
<EndDate>06/23/2008</EndDate>
</PeriodNumber>
<PeriodNumber pn="2">
<StartDate>01/01/2008</StartDate>
<EndDate>03/31/2008</EndDate>
</PeriodNumber>
<PeriodNumber pn="3">
<StartDate>10/01/2007</StartDate>
<EndDate>12/31/2007</EndDate>
</PeriodNumber>
<PeriodNumber pn="4">
<StartDate>07/01/2007</StartDate>
<EndDate>09/30/2007</EndDate>
</PeriodNumber>
</PeriodType>
</Branch>
<Branch id="2">
<PeriodType pt="0">
<PeriodNumber pn="1">
<StartDate>06/23/2006</StartDate>
<EndDate>06/24/2006</EndDate>
</PeriodNumber>
<PeriodNumber pn="2">
<StartDate>06/16/2006</StartDate>
<EndDate>06/22/2006</EndDate>
</PeriodNumber>
<PeriodNumber pn="3">
<StartDate>06/09/2006</StartDate>
<EndDate>06/15/2006</EndDate>
</PeriodNumber>
<PeriodNumber pn="4">
<StartDate>06/02/2006</StartDate>
<EndDate>06/08/2006</EndDate>
</PeriodNumber>
</PeriodType>
<PeriodType pt="1">
<PeriodNumber pn="1">
<StartDate>06/01/2006</StartDate>
<EndDate>06/23/2006</EndDate>
</PeriodNumber>
<PeriodNumber pn="2">
<StartDate>05/01/2006</StartDate>
<EndDate>05/31/2006</EndDate>
</PeriodNumber>
<PeriodNumber pn="3">
<StartDate>04/01/2006</StartDate>
<EndDate>04/30/2006</EndDate>
</PeriodNumber>
<PeriodNumber pn="4">
<StartDate>03/01/2006</StartDate>
<EndDate>03/31/2006</EndDate>
</PeriodNumber>
</PeriodType>
<PeriodType pt="2">
<PeriodNumber pn="1">
<StartDate>04/01/2006</StartDate>
<EndDate>06/23/2006</EndDate>
</PeriodNumber>
<PeriodNumber pn="2">
<StartDate>01/01/2006</StartDate>
<EndDate>03/31/2006</EndDate>
</PeriodNumber>
<PeriodNumber pn="3">
<StartDate>10/01/2005</StartDate>
<EndDate>12/31/2005</EndDate>
</PeriodNumber>
<PeriodNumber pn="4">
<StartDate>07/01/2005</StartDate>
<EndDate>09/30/2005</EndDate>
</PeriodNumber>
</PeriodType>
</Branch>
</root>'
========================
Scott Klein
Author of:
Professional SQL Server 2005 XML
Professional WCF Programming: .NET Development with the Windows Communication Foundation
Professional LINQ
========================
XML question for you XML Gurus
