Here is my xml code:
<IW version="2.0">
<Quote QuoteId="23206810" PackageId="10" EffectiveDate="9/8/2020">
<Vehicle VehicleId="41257627" DriverId="116367578" Description="2009 Toyota COROLLA BASE/S/LE/XLE 4-Door Sedan 2T1BU40E19C181192 ">
<Feature Value="BI" Premium="396">
<Factor>
<Relativity>Driver Class</Relativity>
<RelativityKey>MS21</RelativityKey>
<Value>1.55</Value>
<Rounding>3</Rounding>
<Order>7</Order>
</Factor>
<Factor>
<Relativity>Driver Points</Relativity>
<RelativityKey>0</RelativityKey>
<Value>1</Value>
<Rounding>3</Rounding>
<Order>12</Order>
</Factor>
</Feature>
<Feature Value="PD" Premium="469">
<Factor>
<Relativity>Driver Class</Relativity>
<RelativityKey>MS21</RelativityKey>
<Value>1.55</Value>
<Rounding>3</Rounding>
<Order>7</Order>
</Factor>
<Factor>
<Relativity>Driver Points</Relativity>
<RelativityKey>0</RelativityKey>
<Value>1</Value>
<Rounding>3</Rounding>
<Order>12</Order>
</Factor>
</Feature>
<Feature Value="UMBI" Premium="184">
<Factor>
<Relativity>Driver Class</Relativity>
<RelativityKey>MS21</RelativityKey>
<Value>1.55</Value>
<Rounding>3</Rounding>
<Order>7</Order>
</Factor>
<Factor>
<Relativity>Driver Points</Relativity>
<RelativityKey>0</RelativityKey>
<Value>1</Value>
<Rounding>3</Rounding>
<Order>12</Order>
</Factor>
</Feature>
<Feature Value="UMPD" Premium="78">
<Factor>
<Relativity>Driver Class</Relativity>
<RelativityKey>MS21</RelativityKey>
<Value>1.55</Value>
<Rounding>3</Rounding>
<Order>7</Order>
</Factor>
<Factor>
<Relativity>Driver Points</Relativity>
<RelativityKey>0</RelativityKey>
<Value>1</Value>
<Rounding>3</Rounding>
<Order>12</Order>
</Factor>
</Feature>
<Feature Value="OTC" Premium="192">
<Factor>
<Relativity>Driver Class</Relativity>
<RelativityKey>MS21</RelativityKey>
<Value>1.45</Value>
<Rounding>3</Rounding>
<Order>7</Order>
</Factor>
<Factor>
<Relativity>Driver Points</Relativity>
<RelativityKey>0</RelativityKey>
<Value>1</Value>
<Rounding>3</Rounding>
<Order>12</Order>
</Factor>
</Feature>
<Feature Value="COL" Premium="1334">
<Factor>
<Relativity>Driver Class</Relativity>
<RelativityKey>MS21</RelativityKey>
<Value>2.25</Value>
<Rounding>3</Rounding>
<Order>7</Order>
</Factor>
<Factor>
<Relativity>Driver Points</Relativity>
<RelativityKey>0</RelativityKey>
<Value>1</Value>
<Rounding>3</Rounding>
<Order>12</Order>
</Factor>
</Feature>
</Vehicle>
<Vehicle VehicleId="41257628" DriverId="116367577" Description="2009 Hyundai SANTA FE GLS SPORT UTILITY 4-DR 5NMSG13D79H328179 ">
<Feature Value="BI" Premium="421">
<Factor>
<Relativity>Driver Class</Relativity>
<RelativityKey>FS19</RelativityKey>
<Value>1.65</Value>
<Rounding>3</Rounding>
<Order>7</Order>
</Factor>
<Factor>
<Relativity>Driver Points</Relativity>
<RelativityKey>0</RelativityKey>
<Value>1</Value>
<Rounding>3</Rounding>
<Order>12</Order>
</Factor>
</Feature>
<Feature Value="PD" Premium="511">
<Factor>
<Relativity>Driver Class</Relativity>
<RelativityKey>FS19</RelativityKey>
<Value>1.65</Value>
<Rounding>3</Rounding>
<Order>7</Order>
</Factor>
<Factor>
<Relativity>Driver Points</Relativity>
<RelativityKey>0</RelativityKey>
<Value>1</Value>
<Rounding>3</Rounding>
<Order>12</Order>
</Factor>
</Feature>
<Feature Value="PIP" Premium="403">
<Factor>
<Relativity>Driver Class</Relativity>
<RelativityKey>FS19</RelativityKey>
<Value>1.65</Value>
<Rounding>3</Rounding>
<Order>7</Order>
</Factor>
<Factor>
<Relativity>Driver Points</Relativity>
<RelativityKey>0</RelativityKey>
<Value>1</Value>
<Rounding>3</Rounding>
<Order>12</Order>
</Factor>
</Feature>
<Feature Value="UMBI" Premium="196">
<Factor>
<Relativity>Driver Class</Relativity>
<RelativityKey>FS19</RelativityKey>
<Value>1.65</Value>
<Rounding>3</Rounding>
<Order>7</Order>
</Factor>
<Factor>
<Relativity>Driver Points</Relativity>
<RelativityKey>0</RelativityKey>
<Value>1</Value>
<Rounding>3</Rounding>
<Order>12</Order>
</Factor>
</Feature>
<Feature Value="UMPD" Premium="83">
<Factor>
<Relativity>Driver Class</Relativity>
<RelativityKey>FS19</RelativityKey>
<Value>1.65</Value>
<Rounding>3</Rounding>
<Order>7</Order>
</Factor>
<Factor>
<Relativity>Driver Points</Relativity>
<RelativityKey>0</RelativityKey>
<Value>1</Value>
<Rounding>3</Rounding>
<Order>12</Order>
</Factor>
</Feature>
<Feature Value="OTC" Premium="181">
<Factor>
<Relativity>Driver Class</Relativity>
<RelativityKey>FS19</RelativityKey>
<Value>1.3</Value>
<Rounding>3</Rounding>
<Order>7</Order>
</Factor>
<Factor>
<Relativity>Driver Points</Relativity>
<RelativityKey>0</RelativityKey>
<Value>1</Value>
<Rounding>3</Rounding>
<Order>12</Order>
</Factor>
</Feature>
<Feature Value="COL" Premium="1064">
<Factor>
<Relativity>Driver Class</Relativity>
<RelativityKey>FS19</RelativityKey>
<Value>2</Value>
<Rounding>3</Rounding>
<Order>7</Order>
</Factor>
<Factor>
<Relativity>Driver Points</Relativity>
<RelativityKey>0</RelativityKey>
<Value>1</Value>
<Rounding>3</Rounding>
<Order>12</Order>
</Factor>
</Feature>
</Vehicle>
<Vehicle VehicleId="41257625" DriverId="116367575" Description="2006 Nissan ALTIMA S/SE/SL/SE-R 4-Door Sedan 1N4AL11D36C142780 ">
<Feature Value="BI" Premium="254">
<Factor>
<Relativity>Driver Class</Relativity>
<RelativityKey>FM45</RelativityKey>
<Value>1.15</Value>
<Rounding>3</Rounding>
<Order>7</Order>
</Factor>
<Factor>
<Relativity>Driver Points</Relativity>
<RelativityKey>0</RelativityKey>
<Value>1</Value>
<Rounding>3</Rounding>
<Order>12</Order>
</Factor>
</Feature>
<Feature Value="PD" Premium="301">
<Factor>
<Relativity>Driver Class</Relativity>
<RelativityKey>FM45</RelativityKey>
<Value>1.15</Value>
<Rounding>3</Rounding>
<Order>7</Order>
</Factor>
<Factor>
<Relativity>Driver Points</Relativity>
<RelativityKey>0</RelativityKey>
<Value>1</Value>
<Rounding>3</Rounding>
<Order>12</Order>
</Factor>
</Feature>
<Feature Value="PIP" Premium="286">
<Factor>
<Relativity>Driver Class</Relativity>
<RelativityKey>FM45</RelativityKey>
<Value>1.15</Value>
<Rounding>3</Rounding>
<Order>7</Order>
</Factor>
<Factor>
<Relativity>Driver Points</Relativity>
<RelativityKey>0</RelativityKey>
<Value>1</Value>
<Rounding>3</Rounding>
<Order>12</Order>
</Factor>
</Feature>
<Feature Value="UMBI" Premium="130">
<Factor>
<Relativity>Driver Class</Relativity>
<RelativityKey>FM45</RelativityKey>
<Value>1.15</Value>
<Rounding>3</Rounding>
<Order>7</Order>
</Factor>
<Factor>
<Relativity>Driver Points</Relativity>
<RelativityKey>0</RelativityKey>
<Value>1</Value>
<Rounding>3</Rounding>
<Order>12</Order>
</Factor>
</Feature>
<Feature Value="UMPD" Premium="50">
<Factor>
<Relativity>Driver Class</Relativity>
<RelativityKey>FM45</RelativityKey>
<Value>1.15</Value>
<Rounding>3</Rounding>
<Order>7</Order>
</Factor>
<Factor>
<Relativity>Driver Points</Relativity>
<RelativityKey>0</RelativityKey>
<Value>1</Value>
<Rounding>3</Rounding>
<Order>12</Order>
</Factor>
</Feature>
<Feature Value="OTC" Premium="121">
<Factor>
<Relativity>Driver Class</Relativity>
<RelativityKey>FM45</RelativityKey>
<Value>0.9</Value>
<Rounding>3</Rounding>
<Order>7</Order>
</Factor>
<Factor>
<Relativity>Driver Points</Relativity>
<RelativityKey>0</RelativityKey>
<Value>1</Value>
<Rounding>3</Rounding>
<Order>12</Order>
</Factor>
</Feature>
<Feature Value="COL" Premium="490">
<Factor>
<Relativity>Driver Class</Relativity>
<RelativityKey>FM45</RelativityKey>
<Value>1.05</Value>
<Rounding>3</Rounding>
<Order>7</Order>
</Factor>
<Factor>
<Relativity>Driver Points</Relativity>
<RelativityKey>0</RelativityKey>
<Value>1</Value>
<Rounding>3</Rounding>
<Order>12</Order>
</Factor>
</Feature>
</Vehicle>
</Quote>
</IW>
Here is my T-SQL code. I am not getting any results back.
;WITH CTECastToXml as (
SELECT CAST(qf.FactorXml as xml) as x, nbq.PackageId
FROM dbo.AUT_QuoteFactors qf
INNER JOIN #tempNotBoundQuote nbq
ON qf.QuoteId = nbq.QuoteID
WHERE qf.QuoteId = 23206810
)
SELECT a.qp.value('@QuoteId','varchar(max)') as QuoteID
,a.qp.value('@PackageId','varchar(max)') as PackageId
,b.v.value('@VehicleId', 'varchar(max)') as VehicleId
,b.v.value('@DriverId', 'varchar(max)') as DriverId
,c.v.value('@Value', 'varchar(max)') as Coverage
,CASE
WHEN c.v.value('@Value', 'varchar(max)') = 'BI' THEN c.v.value('@Premium', 'decimal(8,2)')
WHEN c.v.value('@Value', 'varchar(max)') = 'PD' THEN c.v.value('@Premium', 'decimal(8,2)')
WHEN c.v.value('@Value', 'varchar(max)') = 'PIP' THEN c.v.value('@Premium', 'decimal(8,2)')
WHEN c.v.value('@Value', 'varchar(max)') = 'UMBI' THEN c.v.value('@Premium', 'decimal(8,2)')
WHEN c.v.value('@Value', 'varchar(max)') = 'UMPD' THEN c.v.value('@Premium', 'decimal(8,2)')
WHEN c.v.value('@Value', 'varchar(max)') = 'TL' THEN c.v.value('@Premium', 'decimal(8,2)')
WHEN c.v.value('@Value', 'varchar(max)') = 'RR' THEN c.v.value('@Premium', 'decimal(8,2)')
WHEN c.v.value('@Value', 'varchar(max)') = 'OTC' THEN c.v.value('@Premium', 'decimal(8,2)')
WHEN c.v.value('@Value', 'varchar(max)') = 'COL' THEN c.v.value('@Premium', 'decimal(8,2)')
ELSE 0.00
END as Premium
,CASE
WHEN d.v.value('Relativity[1]', 'varchar(max)') = 'Driver Class' THEN e.v.value('.', 'varchar(max)')
ELSE ''
END as DriverClass
--,CASE
-- WHEN d.v.value('Relativity[1]', 'varchar(max)') = 'Driver Points' THEN e.v.value('.', 'varchar(max)')
-- ELSE ''
--END as DriverPoints
FROM CTECastToXml cte
CROSS APPLY x.nodes('/IW/Quote') as a(qp)
CROSS APPLY x.nodes('/IW/Quote/Vehicle') as b(v)
CROSS APPLY x.nodes('/IW/Quote/Vehicle/Feature') as c(v)
**CROSS APPLY x.nodes('/IW/Quote/Vehicle/Feature/Factor/Relativity') as d(v)
CROSS APPLY x.nodes('/IW/Quote/Vehicle/Feature/Factor/RelativityKey') as e(v)**
WHERE a.qp.value('@PackageId','varchar(max)') = cte.PackageId
ORDER BY VehicleId asc, DriverId asc;
Any help/direction would be great. I am not proficient at XML as I don't work with it everyday but the problem appears to be when I added the bolded lines CROSS APPLY BEFORE THE WHERE CLAUSE ... lines above. Thanks.
As DDL and sample data population is not provided, I am guessing that you need something along the following:
T-SQL
-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, FactorXml NVARCHAR(MAX));
INSERT INTO @tbl (FactorXml) VALUES
(N'<IW version="2.0">
<Quote QuoteId="23206810" PackageId="10" EffectiveDate="9/8/2020">
<Vehicle VehicleId="41257627" DriverId="116367578"
Description="2009 Toyota COROLLA BASE/S/LE/XLE 4-Door Sedan 2T1BU40E19C181192 ">
<Feature Value="BI" Premium="396">
<Factor>
<Relativity>Driver Class</Relativity>
<RelativityKey>MS21</RelativityKey>
<Value>1.55</Value>
<Rounding>3</Rounding>
<Order>7</Order>
</Factor>
<Factor>
<Relativity>Driver Points</Relativity>
<RelativityKey>0</RelativityKey>
<Value>1</Value>
<Rounding>3</Rounding>
<Order>12</Order>
</Factor>
</Feature>
<Feature Value="PD" Premium="469">
<Factor>
<Relativity>Driver Class</Relativity>
<RelativityKey>MS21</RelativityKey>
<Value>1.55</Value>
<Rounding>3</Rounding>
<Order>7</Order>
</Factor>
<Factor>
<Relativity>Driver Points</Relativity>
<RelativityKey>0</RelativityKey>
<Value>1</Value>
<Rounding>3</Rounding>
<Order>12</Order>
</Factor>
</Feature>
<Feature Value="UMBI" Premium="184">
<Factor>
<Relativity>Driver Class</Relativity>
<RelativityKey>MS21</RelativityKey>
<Value>1.55</Value>
<Rounding>3</Rounding>
<Order>7</Order>
</Factor>
<Factor>
<Relativity>Driver Points</Relativity>
<RelativityKey>0</RelativityKey>
<Value>1</Value>
<Rounding>3</Rounding>
<Order>12</Order>
</Factor>
</Feature>
<Feature Value="UMPD" Premium="78">
<Factor>
<Relativity>Driver Class</Relativity>
<RelativityKey>MS21</RelativityKey>
<Value>1.55</Value>
<Rounding>3</Rounding>
<Order>7</Order>
</Factor>
<Factor>
<Relativity>Driver Points</Relativity>
<RelativityKey>0</RelativityKey>
<Value>1</Value>
<Rounding>3</Rounding>
<Order>12</Order>
</Factor>
</Feature>
<Feature Value="OTC" Premium="192">
<Factor>
<Relativity>Driver Class</Relativity>
<RelativityKey>MS21</RelativityKey>
<Value>1.45</Value>
<Rounding>3</Rounding>
<Order>7</Order>
</Factor>
<Factor>
<Relativity>Driver Points</Relativity>
<RelativityKey>0</RelativityKey>
<Value>1</Value>
<Rounding>3</Rounding>
<Order>12</Order>
</Factor>
</Feature>
<Feature Value="COL" Premium="1334">
<Factor>
<Relativity>Driver Class</Relativity>
<RelativityKey>MS21</RelativityKey>
<Value>2.25</Value>
<Rounding>3</Rounding>
<Order>7</Order>
</Factor>
<Factor>
<Relativity>Driver Points</Relativity>
<RelativityKey>0</RelativityKey>
<Value>1</Value>
<Rounding>3</Rounding>
<Order>12</Order>
</Factor>
</Feature>
</Vehicle>
<Vehicle VehicleId="41257628" DriverId="116367577"
Description="2009 Hyundai SANTA FE GLS SPORT UTILITY 4-DR 5NMSG13D79H328179 ">
<Feature Value="BI" Premium="421">
<Factor>
<Relativity>Driver Class</Relativity>
<RelativityKey>FS19</RelativityKey>
<Value>1.65</Value>
<Rounding>3</Rounding>
<Order>7</Order>
</Factor>
<Factor>
<Relativity>Driver Points</Relativity>
<RelativityKey>0</RelativityKey>
<Value>1</Value>
<Rounding>3</Rounding>
<Order>12</Order>
</Factor>
</Feature>
<Feature Value="PD" Premium="511">
<Factor>
<Relativity>Driver Class</Relativity>
<RelativityKey>FS19</RelativityKey>
<Value>1.65</Value>
<Rounding>3</Rounding>
<Order>7</Order>
</Factor>
<Factor>
<Relativity>Driver Points</Relativity>
<RelativityKey>0</RelativityKey>
<Value>1</Value>
<Rounding>3</Rounding>
<Order>12</Order>
</Factor>
</Feature>
<Feature Value="PIP" Premium="403">
<Factor>
<Relativity>Driver Class</Relativity>
<RelativityKey>FS19</RelativityKey>
<Value>1.65</Value>
<Rounding>3</Rounding>
<Order>7</Order>
</Factor>
<Factor>
<Relativity>Driver Points</Relativity>
<RelativityKey>0</RelativityKey>
<Value>1</Value>
<Rounding>3</Rounding>
<Order>12</Order>
</Factor>
</Feature>
<Feature Value="UMBI" Premium="196">
<Factor>
<Relativity>Driver Class</Relativity>
<RelativityKey>FS19</RelativityKey>
<Value>1.65</Value>
<Rounding>3</Rounding>
<Order>7</Order>
</Factor>
<Factor>
<Relativity>Driver Points</Relativity>
<RelativityKey>0</RelativityKey>
<Value>1</Value>
<Rounding>3</Rounding>
<Order>12</Order>
</Factor>
</Feature>
<Feature Value="UMPD" Premium="83">
<Factor>
<Relativity>Driver Class</Relativity>
<RelativityKey>FS19</RelativityKey>
<Value>1.65</Value>
<Rounding>3</Rounding>
<Order>7</Order>
</Factor>
<Factor>
<Relativity>Driver Points</Relativity>
<RelativityKey>0</RelativityKey>
<Value>1</Value>
<Rounding>3</Rounding>
<Order>12</Order>
</Factor>
</Feature>
<Feature Value="OTC" Premium="181">
<Factor>
<Relativity>Driver Class</Relativity>
<RelativityKey>FS19</RelativityKey>
<Value>1.3</Value>
<Rounding>3</Rounding>
<Order>7</Order>
</Factor>
<Factor>
<Relativity>Driver Points</Relativity>
<RelativityKey>0</RelativityKey>
<Value>1</Value>
<Rounding>3</Rounding>
<Order>12</Order>
</Factor>
</Feature>
<Feature Value="COL" Premium="1064">
<Factor>
<Relativity>Driver Class</Relativity>
<RelativityKey>FS19</RelativityKey>
<Value>2</Value>
<Rounding>3</Rounding>
<Order>7</Order>
</Factor>
<Factor>
<Relativity>Driver Points</Relativity>
<RelativityKey>0</RelativityKey>
<Value>1</Value>
<Rounding>3</Rounding>
<Order>12</Order>
</Factor>
</Feature>
</Vehicle>
<Vehicle VehicleId="41257625" DriverId="116367575"
Description="2006 Nissan ALTIMA S/SE/SL/SE-R 4-Door Sedan 1N4AL11D36C142780 ">
<Feature Value="BI" Premium="254">
<Factor>
<Relativity>Driver Class</Relativity>
<RelativityKey>FM45</RelativityKey>
<Value>1.15</Value>
<Rounding>3</Rounding>
<Order>7</Order>
</Factor>
<Factor>
<Relativity>Driver Points</Relativity>
<RelativityKey>0</RelativityKey>
<Value>1</Value>
<Rounding>3</Rounding>
<Order>12</Order>
</Factor>
</Feature>
<Feature Value="PD" Premium="301">
<Factor>
<Relativity>Driver Class</Relativity>
<RelativityKey>FM45</RelativityKey>
<Value>1.15</Value>
<Rounding>3</Rounding>
<Order>7</Order>
</Factor>
<Factor>
<Relativity>Driver Points</Relativity>
<RelativityKey>0</RelativityKey>
<Value>1</Value>
<Rounding>3</Rounding>
<Order>12</Order>
</Factor>
</Feature>
<Feature Value="PIP" Premium="286">
<Factor>
<Relativity>Driver Class</Relativity>
<RelativityKey>FM45</RelativityKey>
<Value>1.15</Value>
<Rounding>3</Rounding>
<Order>7</Order>
</Factor>
<Factor>
<Relativity>Driver Points</Relativity>
<RelativityKey>0</RelativityKey>
<Value>1</Value>
<Rounding>3</Rounding>
<Order>12</Order>
</Factor>
</Feature>
<Feature Value="UMBI" Premium="130">
<Factor>
<Relativity>Driver Class</Relativity>
<RelativityKey>FM45</RelativityKey>
<Value>1.15</Value>
<Rounding>3</Rounding>
<Order>7</Order>
</Factor>
<Factor>
<Relativity>Driver Points</Relativity>
<RelativityKey>0</RelativityKey>
<Value>1</Value>
<Rounding>3</Rounding>
<Order>12</Order>
</Factor>
</Feature>
<Feature Value="UMPD" Premium="50">
<Factor>
<Relativity>Driver Class</Relativity>
<RelativityKey>FM45</RelativityKey>
<Value>1.15</Value>
<Rounding>3</Rounding>
<Order>7</Order>
</Factor>
<Factor>
<Relativity>Driver Points</Relativity>
<RelativityKey>0</RelativityKey>
<Value>1</Value>
<Rounding>3</Rounding>
<Order>12</Order>
</Factor>
</Feature>
<Feature Value="OTC" Premium="121">
<Factor>
<Relativity>Driver Class</Relativity>
<RelativityKey>FM45</RelativityKey>
<Value>0.9</Value>
<Rounding>3</Rounding>
<Order>7</Order>
</Factor>
<Factor>
<Relativity>Driver Points</Relativity>
<RelativityKey>0</RelativityKey>
<Value>1</Value>
<Rounding>3</Rounding>
<Order>12</Order>
</Factor>
</Feature>
<Feature Value="COL" Premium="490">
<Factor>
<Relativity>Driver Class</Relativity>
<RelativityKey>FM45</RelativityKey>
<Value>1.05</Value>
<Rounding>3</Rounding>
<Order>7</Order>
</Factor>
<Factor>
<Relativity>Driver Points</Relativity>
<RelativityKey>0</RelativityKey>
<Value>1</Value>
<Rounding>3</Rounding>
<Order>12</Order>
</Factor>
</Feature>
</Vehicle>
</Quote>
</IW>');
-- DDL and sample data population, end
;WITH rs AS
(
SELECT TRY_CAST(FactorXml AS XML) AS xmldata
FROM @tbl
)
SELECT c.value('@QuoteId','INT') as QuoteID
, c.value('@PackageId','INT') as PackageId
, v.value('@VehicleId', 'INT') as VehicleId
, v.value('@DriverId', 'INT') as DriverId
, f.value('@Value', 'VARCHAR(20)') as Coverage
, CASE
WHEN f.value('@Value', 'varchar(20)') IN ('BI','PD','PIP','UMBI','UMPD', 'TL', 'RR', 'OTC', 'COL') THEN f.value('@Premium', 'decimal(8,2)')
ELSE 0.00
END as Premium
, CASE
WHEN fa.value('(Relativity/text())[1]', 'VARCHAR(100)') = 'Driver Class'
THEN fa.value('(RelativityKey/text())[1]', 'VARCHAR(100)')
ELSE ''
END as DriverClass
FROM rs
CROSS APPLY xmldata.nodes('/IW/Quote') AS t(c)
CROSS APPLY t.c.nodes('Vehicle') AS b(v)
CROSS APPLY b.v.nodes('Feature') AS t3(f)
CROSS APPLY t3.f.nodes('Factor') AS d(fa);