SQL XML - 2 набора отдельных данных - ORDER BY & RowNumber
Я объединил 2 набора данных XML, которые извлекаются из ЖЕ ОДНОЙ основной таблицы (dbo.Submission).
Существует поле DATETIME, расположенное в dbo.Submission под названием [AddedDt], я выбираю данные на основе 2 наборов различных критериев, однако я хочу отформатировать возвращаемые данные с помощью ORDER BY [AddedDt], так как эта отправка XML должна идти в хронологический порядок.
Ниже мой оператор SELECT, я оставил ORDER BY в каждом запросе, но это только ЗАКАЗ соответствующего запроса в порядке [AddedDt]. Мне нужно добавить дополнительный узел, который имеет номер строки, я сделал в исходном использовании данных
ROW_NUMBER() OVER ( ORDER BY ( SELECT sub.AddedDt
) ) AS "PolicyRecordNumber" ,
Однако это должно отображаться в элементе "Политика" каждой политики и выполняться последовательно. Как этого добиться?
SELECT ( SELECT sub.Polno AS "Cancellation/PolicyNumber" ,
sub.Term_date AS "Cancellation/CancellationDate" ,
sub.PremXIPT AS "Cancellation/ReturnPremium/NetAmount" ,
sub.PremIPT AS "Cancellation/ReturnPremium/Ipt" ,
sub.FeeXIPT AS "Cancellation/Fee/NetAmount" ,
sub.FeeIPT AS "Cancellation/Fee/Ipt"
FROM dbo.Submissions sub
WHERE sub.[Transaction] = 'Cancellation'
ORDER BY sub.AddedDt
FOR
XML PATH('Policy') ,
TYPE
) ,
( SELECT sub.Polno AS "PCNewBusiness/PolicyNumber" ,
sub.[Inception Date] AS "PCNewBusiness/StartDate" ,
sub.[Renewal Date] AS "PCNewBusiness/StartDate" ,
sub.PremXIPT AS "PCNewBusiness/Premium/NetAmount" ,
sub.PremIPT AS "PCNewBusiness/Premium/IPT" ,
sub.FeeXIPT AS "PCNewBusiness/Fee/NetAmount" ,
sub.FeeIPT AS "PCNewBusiness/Fee/IPT" ,
ph.Title AS "PCNewBusiness/Policyholder/PolicyholderType/PrivateIndividual/Name/Title" ,
ph.Firstname AS "PCNewBusiness/Policyholder/PolicyholderType/PrivateIndividual/Name/FirstNames" ,
ph.Surname AS "PCNewBusiness/Policyholder/PolicyholderType/PrivateIndividual/Name/LastName" ,
ph.DateOfBirth AS "PCNewBusiness/Policyholder/PolicyholderType/PrivateIndividual/DateOfBirth" ,
ph.MaritalStatus AS "PCNewBusiness/Policyholder/PolicyholderType/PrivateIndividual/MaritalStatus" ,
ph.Sex AS "PCNewBusiness/Policyholder/PolicyholderType/PrivateIndividual/Sex" ,
ph.Contact AS "PCNewBusiness/Policyholder/Contact" ,
ph.Addr1 AS "PCNewBusiness/Policyholder/Address/Address1" ,
ph.Addr2 AS "PCNewBusiness/Policyholder/Address/Address2" ,
ph.Addr3 AS "PCNewBusiness/Policyholder/Address/Address3" ,
ph.Addr4 AS "PCNewBusiness/Policyholder/Address/Address4" ,
ph.Pcode AS "PCNewBusiness/Policyholder/Address/Postcode" ,
ISNULL(ph.Tel, ph.Tel2) AS "PCNewBusiness/Policyholder/Phone" ,
ph.Email AS "PCNewBusiness/Policyholder/Email" ,
cov.Cover AS "PCNewBusiness/Cover/CoverType" ,
cov.Excess AS "PCNewBusiness/Cover/PolicyExcess" ,
cov.NCBYears AS "PCNewBusiness/Cover/NoClaimsBonusYears" ,
cov.NCBOtherVeh AS "PCNewBusiness/Cover/NCBOtherVehicle" ,
cov.ClassOfUse AS "PCNewBusiness/Cover/Use" ,
( SELECT dri1.RelationshipToPH 'RelationshipToPolicyholder' ,
( SELECT dri2.Drivertitle 'Title' ,
dri2.FirstName 'FirstNames' ,
dri2.Surname 'LastName'
FROM dbo.Drivers dri2
WHERE dri1.ID = dri2.ID
AND dri1.Driver = dri2.Driver
FOR
XML PATH('Name') ,
TYPE
) ,
dri1.DOB 'DateOfBirth' ,
dri1.Licence 'LicenceType' ,
dri1.LicenceHeld 'PeriodLicenceHeld' ,
( SELECT ph1.Addr1 'Address1' ,
ph1.Addr2 'Address2' ,
ph1.Addr3 'Address3' ,
ph1.Addr4 'Address4' ,
ph1.Pcode 'AddressPostcode'
FROM dbo.PolicyHolder ph1
WHERE ph1.ID = ph.ID
FOR
XML PATH('HomeAddress') ,
TYPE
) ,
dri1.PeriodResident 'PeriodResident' ,
dri1.VehFreq 'VehicleUseFrequency' ,
dri1.OtherVehs 'HasUseOfOtherVehicle' ,
ISNULL(dri1.Occupation, [dri1].[2ndOccupation]) 'MainOccupation' ,
dri1.[2ndOccupation] 'SecondaryOccupation' ,
dri1.RefIns 'MotorInsuranceRefusalDetails' ,
dri1.Accconv 'HasCriminalConvictions' ,
( SELECT 'false' 'Removed' ,
TWClaimType 'ClaimType' ,
IncidentDate ,
Amount
FROM dbo.Claims cla1
WHERE cla1.ID = sub.ID
AND cla1.Driver = dri1.Driver
FOR
XML PATH('DriverClaim') ,
TYPE
) AS "Claims" ,
( SELECT 'false' 'Removed' ,
con1.ConvCode 'ConvictionCode' ,
con1.ConvDate 'ConvictionDate' ,
con1.Amount 'Fine'
FROM dbo.Convictions con1
WHERE con1.ID = sub.ID
AND con1.Driver = dri1.Driver
FOR
XML PATH('DriverConviction') ,
TYPE
) AS "Convictions" ,
( SELECT 'false' 'Removed' ,
med1.Description ,
med1.AgeDiagnosed ,
med1.Medication ,
med1.[DVLA Notified] 'DvlaNotified' ,
med1.CondNote 'ConditionDeteriorating' ,
med1.LicRest 'LicenceRestricted'
FROM dbo.Medical med1
WHERE med1.ID = sub.ID
AND med1.Driver = dri1.Driver
FOR
XML PATH('DriverMedicalCondition') ,
TYPE
) AS "MedicalConditions"
FROM dbo.Drivers dri1
WHERE dri1.ID = sub.ID
FOR
XML PATH('Drivers') ,
TYPE
) AS "PCNewBusiness/Driver" ,
veh.Make AS "PCNewBusiness/Vehicles/Vehicle/Make" ,
veh.Model AS "PCNewBusiness/Vehicles/Vehicle/Model" ,
veh.Reg AS "PCNewBusiness/Vehicles/Vehicle/RegistrationNumber" ,
veh.Body AS "PCNewBusiness/Vehicles/Vehicle/BodyType" ,
veh.Parking AS "PCNewBusiness/Vehicles/Vehicle/LocationWhenNotInUse" ,
( SELECT SUM(Mileage) 'AnnualMileage'
FROM dbo.Drivers dri2
WHERE sub.ID = dri2.ID
) AS "PCNewBusiness/Vehicles/Vehicle/AnnualMileage" ,
veh.YearMade AS "PCNewBusiness/Vehicles/Vehicle/YearOfManufacture" ,
veh.Engine_size AS "PCNewBusiness/Vehicles/Vehicle/EngineSizeCc" ,
veh.Purchasedate AS "PCNewBusiness/Vehicles/Vehicle/PurchaseDate" ,
veh.Value AS "PCNewBusiness/Vehicles/Vehicle/PurchasePrice" ,
veh.Value1 AS "PCNewBusiness/Vehicles/Vehicle/EstimatedValue" ,
veh.Seats AS "PCNewBusiness/Vehicles/Vehicle/NumberOfSeats" ,
veh.RightHandDrive AS "PCNewBusiness/Vehicles/Vehicle/RightHandDrive" ,
veh.Fuel AS "PCNewBusiness/Vehicles/Vehicle/FuelType"
FROM dbo.Submissions sub
LEFT OUTER JOIN dbo.PolicyHolder ph ON ph.ID = sub.ID
LEFT OUTER JOIN dbo.Cover cov ON cov.ID = sub.ID
LEFT OUTER JOIN dbo.Vehicle veh ON veh.ID = sub.ID
WHERE sub.[Transaction] = 'New Business'
ORDER BY sub.AddedDt
FOR
XML PATH('Policy') ,
TYPE
)
FOR XML PATH('Policies');
1 ответ
Вы можете сначала добавить номер текущей записи в CTE. Вы разместили довольно много ненужного кода. В следующий раз, пожалуйста, попробуйте сократить ваш код до нужных строк... Скорее добавьте объявление таблицы и пример данных, чтобы завершить MCVE...
Вот макет вашего сценария
DECLARE @YourTbl TABLE(ID INT IDENTITY, AddedDt DATETIME,GroupingValue VARCHAR(100),SomeValue VARCHAR(100));
INSERT INTO @YourTbl VALUES
({ts'2016-01-01 00:00:00'},'A','Value A1')
,({ts'2016-01-02 00:00:00'},'B','Value B1')
,({ts'2016-01-03 00:00:00'},'A','Value A2')
,({ts'2016-01-04 00:00:00'},'A','Value A3')
,({ts'2016-01-05 00:00:00'},'B','Value B3')
,({ts'2016-01-06 00:00:00'},'B','Value B3')
,({ts'2016-01-07 00:00:00'},'A','Value A4');
- Это CTE, который добавляет номер
WITH NumberedCTE AS
(
SELECT ROW_NUMBER() OVER(ORDER BY AddedDt) AS PolicyRecordNumber
,*
FROM @YourTbl
)
- И это групповое создание XML
SELECT t1.GroupingValue AS [@ID]
,(
SELECT AddedDt AS [@dt]
,SomeValue AS [@val]
,PolicyRecordNumber AS [@rn]
FROM NumberedCTE AS t2
WHERE t1.GroupingValue=t2.GroupingValue
ORDER BY PolicyRecordNumber
FOR XML PATH('Detail'),TYPE
)
FROM NumberedCTE AS t1
GROUP BY t1.GroupingValue
FOR XML PATH('Group'),ROOT('root')
- Результат заказан изнутри и имеет оригинальный номер записи
<root>
<Group ID="A">
<Detail dt="2016-01-01T00:00:00" val="Value A1" rn="1" />
<Detail dt="2016-01-03T00:00:00" val="Value A2" rn="3" />
<Detail dt="2016-01-04T00:00:00" val="Value A3" rn="4" />
<Detail dt="2016-01-07T00:00:00" val="Value A4" rn="7" />
</Group>
<Group ID="B">
<Detail dt="2016-01-02T00:00:00" val="Value B1" rn="2" />
<Detail dt="2016-01-05T00:00:00" val="Value B3" rn="5" />
<Detail dt="2016-01-06T00:00:00" val="Value B3" rn="6" />
</Group>
</root>