September 23, 2016 at 4:55 am
Hi,
So I am trying to modify a query that extracts the data from an XML column into a desired format with a rank for the repeating tags.
But we are facing a couple of issues.
The XML contains some tags that are only in the XML once. Then there is a repeating section - RSQPL. Each grouping as a CSN number, in this case two. But some of the tags are optional. So using windows function you get a Rank but if the optional tag only appears in the second group it gets a rank of 1. So it appears as though it is part of the first group.
Test data table def and test row, with destination table and expected results.
CREATE TABLE [dbo].[InsurancePolicyHistory]
(
[InsurancePolicyID] [bigint] NOT NULL,
[VersionID] [smallint] NOT NULL,
[Status] [tinyint] NOT NULL,
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NOT NULL,
[ApplicationXML] [xml] NOT NULL,
[LastModifiedDate] [datetime] NOT NULL
)
GO
Not sure if this is the best way to insert the XML into the column though!
INSERT INTO [dbo].[InsurancePolicyHistory]
SELECT 1, 0, 0, '20150101', '20151231',
'
<AnswersDefinition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Version>
<VersionNumber>100</VersionNumber>
</Version>
<HD key="HiddenRuntimeData">
<Item><?xml version="1.0" encoding="utf-16"?>
<ArrayOfKeyedItemOfString xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<KeyedItemOfString key="CreditRating">
<Item />
</KeyedItemOfString>
</ArrayOfKeyedItemOfString></Item>
</HD>
<HD key="QuoteInfo">
<Item><?xml version="1.0" encoding="utf-16"?><QuoteInfo xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><RenewalCount>1</RenewalCount><ProductID xsi:type="xsd:int">400</ProductID><Key>QuoteInfo</Key></QuoteInfo></Item>
</HD>
<HD key="HiddenValueRenewalUpdatePointData">
<Item><?xml version="1.0" encoding="utf-16"?><RenewalUpdatePointData xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><RenewalUpdatePoints /></RenewalUpdatePointData></Item>
</HD>
<HD key="Rates">
<Item><?xml version="1.0" encoding="utf-16"?><ArrayOfRate xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><Rate><Type>Tax</Type><Value>32</Value><FrozenOnStartOfCover>true</FrozenOnStartOfCover><Key>Tax</Key></Rate></ArrayOfRate></Item>
</HD>
<PL>
<AP>
<N />
<AL>
<APS>
<T>PolicyHolder Info</T>
<AL>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>1111111111</DV>
</DVL>
<VL>
<V>1111111111</V>
</VL>
<QT>CustomerAndInsuredPersonSocialSecurityTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Mary</DV>
</DVL>
<VL>
<V>Mary</V>
</VL>
<QT>CustomerAndInsuredPersonFirstNameTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL />
<VL />
<QT>CustomerAndInsuredPersonLastNameTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>9999</DV>
</DVL>
<VL>
<V>9999</V>
</VL>
<QT>CampaignCodeTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Domestic</DV>
</DVL>
<VL>
<V>Domestic</V>
</VL>
<QT>AddrLine1Tag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>AA1 1AA</DV>
</DVL>
<VL>
<V>AA1 1AA</V>
</VL>
<QT>AddrPostCodeTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Wessex</DV>
</DVL>
<VL>
<V>Wessex</V>
</VL>
<QT>AddrLine3Tag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL />
<VL />
<QT>CreditScoreTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL />
<VL />
<QT>StopQuoteTag</QT>
</AWT>
</AL>
</APS>
<APS>
<T>Contct Name</T>
<AL>
<AWT>
<VAL>true</VAL>
<DVL />
<VL />
<QT>FirstNameTag_1</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL />
<VL />
<QT>LastNameTag_1</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL />
<VL />
<QT>TelephoneNumberTag_1</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL />
<VL />
<QT>TitleTag_1</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL />
<VL />
<QT>FirstNameTag_2</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL />
<VL />
<QT>LastNameTag_2</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL />
<VL />
<QT>TelephoneNumberTag_2</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL />
<VL />
<QT>TitleTag_2</QT>
</AWT>
</AL>
</APS>
<APS>
<T>Insurance Info</T>
<AL>
<ARSQ>
<VAL>true</VAL>
<QT>CompanyHealth_RepeatingTag</QT>
<NCSN>3</NCSN>
<RSQPL>
<ASQL>
<CSN>1</CSN>
<SQPL>
<AP>
<N />
<AL>
<APS>
<T>Insured Person</T>
<AL>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>999999999999</DV>
</DVL>
<VL>
<V>999999999999</V>
</VL>
<QT>InsuredPersonSocialSecurityTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Joanne</DV>
</DVL>
<VL>
<V>Joanne</V>
</VL>
<QT>InsuredPersonFirstNameTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Smith</DV>
</DVL>
<VL>
<V>Smith</V>
</VL>
<QT>InsuredPersonLastNameTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>1990-01-01</DV>
</DVL>
<VL>
<V>1990-01-01</V>
</VL>
<QT>CompanyHealth_DateOfBirthTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>26</DV>
</DVL>
<VL>
<V>26</V>
</VL>
<QT>CompanyHealth_AgeofInsuredPersonTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Full</DV>
</DVL>
<VL>
<V>Full</V>
</VL>
<QT>CompanyHealth_TypeofInsuredTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Yes</DV>
</DVL>
<VL>
<V>Yes</V>
</VL>
<QT>CompanyHealth_FitForWorkTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Yes</DV>
</DVL>
<VL>
<V>Yes</V>
</VL>
<QT>CompanyHealth_CompanyInsuredWithZZZZZTag</QT>
</AWT>
</AL>
</APS>
<APS>
<T>Insurance Info</T>
<AL>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>No</DV>
</DVL>
<VL>
<V>No</V>
</VL>
<QT>CompanyHealth_LifeInsuranceTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Yes</DV>
</DVL>
<VL>
<V>Yes</V>
</VL>
<QT>CompanyHealth_AccidentalInsuranceTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>1000 Sterling</DV>
</DVL>
<VL>
<V>1000 Sterling</V>
</VL>
<QT>CompanyHealth_AccidentalAmountTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Yes</DV>
</DVL>
<VL>
<V>Yes</V>
</VL>
<QT>CompanyHealth_HealthCareInsuranceTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Tier 1</DV>
</DVL>
<VL>
<V>Tier 1</V>
</VL>
<QT>CompanyHealth_HealthCarePackageTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>No</DV>
</DVL>
<VL>
<V>No</V>
</VL>
<QT>CompanyHealth_HealthCareConversionTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>No</DV>
</DVL>
<VL>
<V>No</V>
</VL>
<QT>CompanyHealth_SicknessBITag</QT>
</AWT>
</AL>
</APS>
</AL>
</AP>
</SQPL>
</ASQL>
<ASQL>
<CSN>2</CSN>
<SQPL>
<AP>
<N />
<AL>
<APS>
<T>Insured Info</T>
<AL>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>888888888888</DV>
</DVL>
<VL>
<V>888888888888</V>
</VL>
<QT>InsuredPersonSocialSecurityTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Kim</DV>
</DVL>
<VL>
<V>Kim</V>
</VL>
<QT>InsuredPersonFirstNameTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Smith</DV>
</DVL>
<VL>
<V>Smith</V>
</VL>
<QT>InsuredPersonLastNameTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>1970-01-01</DV>
</DVL>
<VL>
<V>1970-01-01</V>
</VL>
<QT>CompanyHealth_DateOfBirthTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>46</DV>
</DVL>
<VL>
<V>43</V>
</VL>
<QT>CompanyHealth_AgeofInsuredPersonTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Full</DV>
</DVL>
<VL>
<V>Full</V>
</VL>
<QT>CompanyHealth_TypeofInsuredTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Yes</DV>
</DVL>
<VL>
<V>Yes</V>
</VL>
<QT>CompanyHealth_FitForWorkTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Yes</DV>
</DVL>
<VL>
<V>Yes</V>
</VL>
<QT>CompanyHealth_CompanyInsuredWithZZZZZTag</QT>
</AWT>
</AL>
</APS>
<APS>
<T>Insured Info</T>
<AL>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Yes</DV>
</DVL>
<VL>
<V>Yes</V>
</VL>
<QT>CompanyHealth_LifeInsuranceTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>500 Sterling</DV>
</DVL>
<VL>
<V>500 Sterling</V>
</VL>
<QT>CompanyHealth_LifeProtectionTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Yes</DV>
</DVL>
<VL>
<V>Yes</V>
</VL>
<QT>CompanyHealth_AccidentalInsuranceTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>1000 Sterling</DV>
</DVL>
<VL>
<V>1000 Sterling</V>
</VL>
<QT>CompanyHealth_AccidentalAmountTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Yes</DV>
</DVL>
<VL>
<V>Yes</V>
</VL>
<QT>CompanyHealth_HealthCareInsuranceTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Tier 1</DV>
</DVL>
<VL>
<V>Tier 1</V>
</VL>
<QT>CompanyHealth_HealthCarePackageTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>No</DV>
</DVL>
<VL>
<V>No</V>
</VL>
<QT>CompanyHealth_HealthCareConversionTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Yes</DV>
</DVL>
<VL>
<V>Yes</V>
</VL>
<QT>CompanyHealth_SicknessBITag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>20 Sterling</DV>
</DVL>
<VL>
<V>20 Sterling</V>
</VL>
<QT>CompanyHealth_OverheadCostTag</QT>
</AWT>
</AL>
</APS>
</AL>
</AP>
</SQPL>
</ASQL>
</RSQPL>
</ARSQ>
</AL>
</APS>
<APS>
<T>Other Info</T>
<AL>
<AWT>
<VAL>true</VAL>
<DVL />
<VL />
<QT>ExternalTextTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL />
<VL />
<QT>InternalTextTag</QT>
</AWT>
</AL>
</APS>
<APS>
<T>Period of Insurance</T>
<AL>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>2015-01-01</DV>
</DVL>
<VL>
<V>2015-01-01</V>
</VL>
<QT>StartCoverTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>2015-12-31</DV>
</DVL>
<VL>
<V>2015-12-31</V>
</VL>
<QT>EndCoverTag</QT>
</AWT>
</AL>
</APS>
</AL>
</AP>
</PL>
</AnswersDefinition>'
, '20160922'
;
Dest Table
CREATE TABLE PolicyDestination
(
TagID INT,
TageName VARCHAR(50),
InsurancePolicyID INT,
VersionID INT,
[Rank] INT,
[Value] VARCHAR(50),
DisplayValue VARCHAR(50),
ProductID INT
)
Test Results
INSERT INTO PolicyDestination
VALUES
(1, 'AddrLine1Tag', 1, 0, 1, 'Domestic', 'Domestic', 2),
(2, 'AddrLine3Tag', 1, 0, 1, 'Wessex', 'Wessex', 2),
(3, 'AddrPostCodeTag', 1, 0, 1, 'AA1 1AA', 'AA1 1AA', 2),
(4, 'CampaignCodeTag', 1, 0, 1, '9999', '9999', 2),
(5, 'CompanyHealth_AccidentalAmountTag', 1, 0, 1, '1000 Sterling', '1000 Sterling', 2),
(5, 'CompanyHealth_AccidentalAmountTag', 1, 0, 2, '500 Sterling', '500 Sterling', 2),
(6, 'CompanyHealth_AccidentalInsuranceTag', 1, 0, 1, 'Yes', 'Yes', 2),
(6, 'CompanyHealth_AccidentalInsuranceTag', 1, 0, 2, 'Yes', 'Yes', 2),
(7, 'CompanyHealth_AgeofInsuredPersonTag', 1, 0, 1, '26', '26', 2),
(7, 'CompanyHealth_AgeofInsuredPersonTag', 1, 0, 2, '46', '46', 2),
(8, 'CompanyHealth_CompanyInsuredWithZZZZZTag', 1, 0, 1, 'Yes', 'Yes', 2),
(8, 'CompanyHealth_CompanyInsuredWithZZZZZTag', 1, 0, 2, 'Yes', 'Yes', 2),
(9, 'CompanyHealth_DateOfBirthTag', 1, 0, 1, '32874', '32874', 2),
(9, 'CompanyHealth_DateOfBirthTag', 1, 0, 2, '25569', '25569', 2),
(10, 'CompanyHealth_FitForWorkTag', 1, 0, 1, 'Yes', 'Yes', 2),
(10, 'CompanyHealth_FitForWorkTag', 1, 0, 2, 'Yes', 'Yes', 2),
(11, 'CompanyHealth_HealthCareConversionTag', 1, 0, 1, 'No', 'No', 2),
(11, 'CompanyHealth_HealthCareConversionTag', 1, 0, 2, 'No', 'No', 2),
(12, 'CompanyHealth_HealthCareInsuranceTag', 1, 0, 1, 'Yes', 'Yes', 2),
(12, 'CompanyHealth_HealthCareInsuranceTag', 1, 0, 2, 'Yes', 'Yes', 2),
(13, 'CompanyHealth_HealthCarePackageTag', 1, 0, 1, 'Tier 1', 'Tier 1', 2),
(13, 'CompanyHealth_HealthCarePackageTag', 1, 0, 2, 'Tier 1', 'Tier 1', 2),
(14, 'CompanyHealth_LifeInsuranceTag', 1, 0, 1, 'No', 'No', 2),
(14, 'CompanyHealth_LifeInsuranceTag', 1, 0, 2, 'Yes', 'Yes', 2),
(15, 'CompanyHealth_LifeProtectionTag', 1, 0, 2, '500 Sterling', '500 Sterling', 2),
(16, 'CompanyHealth_OverheadCostTag', 1, 0, 2, '20 Sterling', '20 Sterling', 2),
(17, 'CompanyHealth_SicknessBITag', 1, 0, 1, 'No', 'No', 2),
(17, 'CompanyHealth_SicknessBITag', 1, 0, 2, 'Yes', 'Yes', 2),
(18, 'CompanyHealth_TypeofInsuredTag', 1, 0, 1, 'Full', 'Full', 2),
(18, 'CompanyHealth_TypeofInsuredTag', 1, 0, 2, 'Tier 1', 'Tier 1', 2),
(19, 'CreditScoreTag', 1, 0, 1, NULL, NULL, 2),
(20, 'CustomerAndInsuredPersonFirstNameTag', 1, 0, 1, 'Mary', 'Mary', 2),
(21, 'CustomerAndInsuredPersonLastNameTag', 1, 0, 1, NULL, NULL, 2),
(22, 'CustomerAndInsuredPersonSocialSecurityTag', 1, 0, 1, '1111111111', '1111111111', 2),
(23, 'EndCoverTag', 1, 0, 1, '42369', '42369', 2),
(24, 'ExternalTextTag', 1, 0, 1, NULL, NULL, 2),
(25, 'FirstNameTag_1', 1, 0, 1, NULL, NULL, 2),
(26, 'FirstNameTag_2', 1, 0, 1, NULL, NULL, 2),
(27, 'InsuredPersonFirstNameTag', 1, 0, 1, 'Joanne', 'Joanne', 2),
(27, 'InsuredPersonFirstNameTag', 1, 0, 2, 'Kim', 'Kim', 2),
(28, 'InsuredPersonLastNameTag', 1, 0, 1, 'Smith', 'Smith', 2),
(28, 'InsuredPersonLastNameTag', 1, 0, 2, 'Smith', 'Smith', 2),
(29, 'InsuredPersonSocialSecurityTag', 1, 0, 1, '999999999999', '999999999999', 2),
(29, 'InsuredPersonSocialSecurityTag', 1, 0, 2, '888888888888', '888888888888', 2),
(30, 'InternalTextTag', 1, 0, 1, NULL, NULL, 2),
(31, 'LastNameTag_1', 1, 0, 1, NULL, NULL, 2),
(32, 'LastNameTag_2', 1, 0, 1, NULL, NULL, 2),
(33, 'StartCoverTag', 1, 0, 1, '42005', '42005', 2),
(34, 'StopQuoteTag', 1, 0, 1, NULL, NULL, 2),
(35, 'TelephoneNumberTag_1', 1, 0, 1, NULL, NULL, 2),
(36, 'TelephoneNumberTag_2', 1, 0, 1, NULL, NULL, 2),
(37, 'TitleTag_1', 1, 0, 1, NULL, NULL, 2),
(38, 'TitleTag_2', 1, 0, 1, NULL, NULL, 2);
This is the original query;
SELECT
[InsurancePolicyHistory].[InsurancePolicyID] AS [InsurancePolicyID],
[InsurancePolicyHistory].[VersionID] AS [VersionID],
ROW_NUMBER() OVER (PARTITION BY [InsurancePolicyHistory].[InsurancePolicyID], A.[policy].value('(QT/text())[1]', 'NVARCHAR(64)') ORDER BY [InsurancePolicyHistory].[InsurancePolicyID]) AS [Rank],
A.[policy].value('(QT/text())[1]', 'NVARCHAR(64)') AS [TagName],
A.[policy].value('(VL/V/text())[1]', 'NVARCHAR(128)') AS [Value],
A.[policy].value('(DVL/DV/text())[1]', 'NVARCHAR(128)') AS [DisplayValue]
FROM
[InsurancePolicyHistory]
CROSS APPLY
[InsurancePolicyHistory].[ApplicationXML].nodes('//AWT') AS A(policy)
WHERE
[InsurancePolicyHistory].[InsurancePolicyID] = 1
But for tags = CompanyHealth_LifeProtectionTag and CompanyHealth_OverheadCostTag the Rank should be 2 as they only appear in the second repeating group. But the ROW_NUMBER counts as 1 as they only appear once!
So then I tried to include the CSN tag
SELECT
[InsurancePolicyHistory].[InsurancePolicyID] AS [InsurancePolicyID],
[InsurancePolicyHistory].[VersionID] AS [VersionID],
B.[CSN].value('(text())[1]', 'INT') AS [Rank],
A.[policy].value('(QT/text())[1]', 'NVARCHAR(64)') AS [TagName],
A.[policy].value('(VL/V/text())[1]', 'NVARCHAR(128)') AS [Value],
A.[policy].value('(DVL/DV/text())[1]', 'NVARCHAR(128)') AS [DisplayValue]
FROM
[InsurancePolicyHistory]
CROSS APPLY
[InsurancePolicyHistory].[ApplicationXML].nodes('//AWT') AS A(policy)
CROSS APPLY
[InsurancePolicyHistory].[ApplicationXML].nodes('//CSN') AS B(CSN)
WHERE
[InsurancePolicyHistory].[InsurancePolicyID] = 1
But due to the structure of the XML and my limited knowledge of querying XML I now get too many rows! 106 in this case. Should be 53. Even using DISTINCT only reduces it to 88
I have tried a few other combinations, using a CTE and splitting the query up and they also have failed to produced 53 rows with the expected ranking. One attempt only returned 23 rows.
Any help with this much appreciated.
If you want me to post the other queries I have tried let me know. I still have some of them saved.
Regards,
Rodney
September 23, 2016 at 5:16 am
Oh and a couple of other things. The non repeating tags (header and footer in my head) are counted as rank 1, along with the first repeating group. Its for when a policy covers more than one person, or vehicle etc.
I did wonder if its possible to extract the non-repeating tags in one query and then a second for the repeating section. But I got really stuck on even getting a query to work.
Rodney
September 23, 2016 at 6:21 am
rodjkidd (9/23/2016)
Oh and a couple of other things. The non repeating tags (header and footer in my head) are counted as rank 1, along with the first repeating group. Its for when a policy covers more than one person, or vehicle etc.I did wonder if its possible to extract the non-repeating tags in one query and then a second for the repeating section. But I got really stuck on even getting a query to work.
Rodney
This looks to me as relatively simple task but the XML example contains multiple "<?xml version="1.0" encoding="utf-16"?>" tags which throws an error when trying to run it, could you please clean that up;-)
😎
September 23, 2016 at 7:56 am
Cleaned up the XML, here is a query that brings back the desired tags by the looks of it, not all the values are matching though???.
😎
DECLARE @TXML XML = N'<?xml version="1.0" encoding="utf-16"?>
<AnswersDefinition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Version>
<VersionNumber>100</VersionNumber>
</Version>
<HD key="HiddenRuntimeData">
<Item>
<ArrayOfKeyedItemOfString xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<KeyedItemOfString key="CreditRating">
<Item />
</KeyedItemOfString>
</ArrayOfKeyedItemOfString></Item>
</HD>
<HD key="QuoteInfo">
<Item><QuoteInfo xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><RenewalCount>1</RenewalCount><ProductID xsi:type="xsd:int">400</ProductID><Key>QuoteInfo</Key></QuoteInfo></Item>
</HD>
<HD key="HiddenValueRenewalUpdatePointData">
<Item><RenewalUpdatePointData xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><RenewalUpdatePoints /></RenewalUpdatePointData></Item>
</HD>
<HD key="Rates">
<Item><ArrayOfRate xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><Rate><Type>Tax</Type><Value>32</Value><FrozenOnStartOfCover>true</FrozenOnStartOfCover><Key>Tax</Key></Rate></ArrayOfRate></Item>
</HD>
<PL>
<AP>
<N />
<AL>
<APS>
<T>PolicyHolder Info</T>
<AL>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>1111111111</DV>
</DVL>
<VL>
<V>1111111111</V>
</VL>
<QT>CustomerAndInsuredPersonSocialSecurityTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Mary</DV>
</DVL>
<VL>
<V>Mary</V>
</VL>
<QT>CustomerAndInsuredPersonFirstNameTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL />
<VL />
<QT>CustomerAndInsuredPersonLastNameTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>9999</DV>
</DVL>
<VL>
<V>9999</V>
</VL>
<QT>CampaignCodeTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Domestic</DV>
</DVL>
<VL>
<V>Domestic</V>
</VL>
<QT>AddrLine1Tag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>AA1 1AA</DV>
</DVL>
<VL>
<V>AA1 1AA</V>
</VL>
<QT>AddrPostCodeTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Wessex</DV>
</DVL>
<VL>
<V>Wessex</V>
</VL>
<QT>AddrLine3Tag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL />
<VL />
<QT>CreditScoreTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL />
<VL />
<QT>StopQuoteTag</QT>
</AWT>
</AL>
</APS>
<APS>
<T>Contct Name</T>
<AL>
<AWT>
<VAL>true</VAL>
<DVL />
<VL />
<QT>FirstNameTag_1</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL />
<VL />
<QT>LastNameTag_1</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL />
<VL />
<QT>TelephoneNumberTag_1</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL />
<VL />
<QT>TitleTag_1</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL />
<VL />
<QT>FirstNameTag_2</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL />
<VL />
<QT>LastNameTag_2</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL />
<VL />
<QT>TelephoneNumberTag_2</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL />
<VL />
<QT>TitleTag_2</QT>
</AWT>
</AL>
</APS>
<APS>
<T>Insurance Info</T>
<AL>
<ARSQ>
<VAL>true</VAL>
<QT>CompanyHealth_RepeatingTag</QT>
<NCSN>3</NCSN>
<RSQPL>
<ASQL>
<CSN>1</CSN>
<SQPL>
<AP>
<N />
<AL>
<APS>
<T>Insured Person</T>
<AL>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>999999999999</DV>
</DVL>
<VL>
<V>999999999999</V>
</VL>
<QT>InsuredPersonSocialSecurityTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Joanne</DV>
</DVL>
<VL>
<V>Joanne</V>
</VL>
<QT>InsuredPersonFirstNameTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Smith</DV>
</DVL>
<VL>
<V>Smith</V>
</VL>
<QT>InsuredPersonLastNameTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>1990-01-01</DV>
</DVL>
<VL>
<V>1990-01-01</V>
</VL>
<QT>CompanyHealth_DateOfBirthTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>26</DV>
</DVL>
<VL>
<V>26</V>
</VL>
<QT>CompanyHealth_AgeofInsuredPersonTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Full</DV>
</DVL>
<VL>
<V>Full</V>
</VL>
<QT>CompanyHealth_TypeofInsuredTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Yes</DV>
</DVL>
<VL>
<V>Yes</V>
</VL>
<QT>CompanyHealth_FitForWorkTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Yes</DV>
</DVL>
<VL>
<V>Yes</V>
</VL>
<QT>CompanyHealth_CompanyInsuredWithZZZZZTag</QT>
</AWT>
</AL>
</APS>
<APS>
<T>Insurance Info</T>
<AL>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>No</DV>
</DVL>
<VL>
<V>No</V>
</VL>
<QT>CompanyHealth_LifeInsuranceTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Yes</DV>
</DVL>
<VL>
<V>Yes</V>
</VL>
<QT>CompanyHealth_AccidentalInsuranceTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>1000 Sterling</DV>
</DVL>
<VL>
<V>1000 Sterling</V>
</VL>
<QT>CompanyHealth_AccidentalAmountTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Yes</DV>
</DVL>
<VL>
<V>Yes</V>
</VL>
<QT>CompanyHealth_HealthCareInsuranceTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Tier 1</DV>
</DVL>
<VL>
<V>Tier 1</V>
</VL>
<QT>CompanyHealth_HealthCarePackageTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>No</DV>
</DVL>
<VL>
<V>No</V>
</VL>
<QT>CompanyHealth_HealthCareConversionTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>No</DV>
</DVL>
<VL>
<V>No</V>
</VL>
<QT>CompanyHealth_SicknessBITag</QT>
</AWT>
</AL>
</APS>
</AL>
</AP>
</SQPL>
</ASQL>
<ASQL>
<CSN>2</CSN>
<SQPL>
<AP>
<N />
<AL>
<APS>
<T>Insured Info</T>
<AL>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>888888888888</DV>
</DVL>
<VL>
<V>888888888888</V>
</VL>
<QT>InsuredPersonSocialSecurityTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Kim</DV>
</DVL>
<VL>
<V>Kim</V>
</VL>
<QT>InsuredPersonFirstNameTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Smith</DV>
</DVL>
<VL>
<V>Smith</V>
</VL>
<QT>InsuredPersonLastNameTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>1970-01-01</DV>
</DVL>
<VL>
<V>1970-01-01</V>
</VL>
<QT>CompanyHealth_DateOfBirthTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>46</DV>
</DVL>
<VL>
<V>43</V>
</VL>
<QT>CompanyHealth_AgeofInsuredPersonTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Full</DV>
</DVL>
<VL>
<V>Full</V>
</VL>
<QT>CompanyHealth_TypeofInsuredTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Yes</DV>
</DVL>
<VL>
<V>Yes</V>
</VL>
<QT>CompanyHealth_FitForWorkTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Yes</DV>
</DVL>
<VL>
<V>Yes</V>
</VL>
<QT>CompanyHealth_CompanyInsuredWithZZZZZTag</QT>
</AWT>
</AL>
</APS>
<APS>
<T>Insured Info</T>
<AL>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Yes</DV>
</DVL>
<VL>
<V>Yes</V>
</VL>
<QT>CompanyHealth_LifeInsuranceTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>500 Sterling</DV>
</DVL>
<VL>
<V>500 Sterling</V>
</VL>
<QT>CompanyHealth_LifeProtectionTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Yes</DV>
</DVL>
<VL>
<V>Yes</V>
</VL>
<QT>CompanyHealth_AccidentalInsuranceTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>1000 Sterling</DV>
</DVL>
<VL>
<V>1000 Sterling</V>
</VL>
<QT>CompanyHealth_AccidentalAmountTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Yes</DV>
</DVL>
<VL>
<V>Yes</V>
</VL>
<QT>CompanyHealth_HealthCareInsuranceTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Tier 1</DV>
</DVL>
<VL>
<V>Tier 1</V>
</VL>
<QT>CompanyHealth_HealthCarePackageTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>No</DV>
</DVL>
<VL>
<V>No</V>
</VL>
<QT>CompanyHealth_HealthCareConversionTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Yes</DV>
</DVL>
<VL>
<V>Yes</V>
</VL>
<QT>CompanyHealth_SicknessBITag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>20 Sterling</DV>
</DVL>
<VL>
<V>20 Sterling</V>
</VL>
<QT>CompanyHealth_OverheadCostTag</QT>
</AWT>
</AL>
</APS>
</AL>
</AP>
</SQPL>
</ASQL>
</RSQPL>
</ARSQ>
</AL>
</APS>
<APS>
<T>Other Info</T>
<AL>
<AWT>
<VAL>true</VAL>
<DVL />
<VL />
<QT>ExternalTextTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL />
<VL />
<QT>InternalTextTag</QT>
</AWT>
</AL>
</APS>
<APS>
<T>Period of Insurance</T>
<AL>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>2015-01-01</DV>
</DVL>
<VL>
<V>2015-01-01</V>
</VL>
<QT>StartCoverTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>2015-12-31</DV>
</DVL>
<VL>
<V>2015-12-31</V>
</VL>
<QT>EndCoverTag</QT>
</AWT>
</AL>
</APS>
</AL>
</AP>
</PL>
</AnswersDefinition>';
SELECT
ANSWDF.DATA.value('(T/text())[1]','VARCHAR(100)') AS NODE_NAME
,DENSE_RANK() OVER
(
ORDER BY AWT.DATA.value('(QT/text())[1]','VARCHAR(100)')
) AS TAG_ID
,ROW_NUMBER() OVER
(
PARTITION BY AWT.DATA.value('(QT/text())[1]','VARCHAR(100)')
ORDER BY @@VERSION
) AS TAG_RANK
,AWT.DATA.value('(QT/text())[1]' ,'VARCHAR(100)') AS TAG_NAME
,AWT.DATA.value('(VAL/text())[1]' ,'VARCHAR(100)') AS TAG_VAL
,AWT.DATA.value('(DVL/DV/text())[1]','VARCHAR(100)') AS TAG_DISP_VALUE
,AWT.DATA.value('(VL/V/text())[1]' ,'VARCHAR(100)') AS TAG_VALUE
FROM @TXML.nodes('AnswersDefinition/PL/AP/AL/APS') ANSWDF(DATA)
CROSS APPLY ANSWDF.DATA.nodes('AL//AWT') AWT(DATA)
ORDER BY AWT.DATA.value('(QT/text())[1]','VARCHAR(100)') ASC;
Output from the sample XML
NODE_NAME TAG_ID TAG_RANK TAG_NAME TAG_VAL TAG_DISP_VALUE TAG_VALUE
---------------------- ------- --------- ------------------------------------------ -------- --------------- -----------------
PolicyHolder Info 1 1 AddrLine1Tag true Domestic Domestic
PolicyHolder Info 2 1 AddrLine3Tag true Wessex Wessex
PolicyHolder Info 3 1 AddrPostCodeTag true AA1 1AA AA1 1AA
PolicyHolder Info 4 1 CampaignCodeTag true 9999 9999
Insurance Info 5 1 CompanyHealth_AccidentalAmountTag true 1000 Sterling 1000 Sterling
Insurance Info 5 2 CompanyHealth_AccidentalAmountTag true 1000 Sterling 1000 Sterling
Insurance Info 6 1 CompanyHealth_AccidentalInsuranceTag true Yes Yes
Insurance Info 6 2 CompanyHealth_AccidentalInsuranceTag true Yes Yes
Insurance Info 7 1 CompanyHealth_AgeofInsuredPersonTag true 26 26
Insurance Info 7 2 CompanyHealth_AgeofInsuredPersonTag true 46 43
Insurance Info 8 1 CompanyHealth_CompanyInsuredWithZZZZZTag true Yes Yes
Insurance Info 8 2 CompanyHealth_CompanyInsuredWithZZZZZTag true Yes Yes
Insurance Info 9 1 CompanyHealth_DateOfBirthTag true 1990-01-01 1990-01-01
Insurance Info 9 2 CompanyHealth_DateOfBirthTag true 1970-01-01 1970-01-01
Insurance Info 10 1 CompanyHealth_FitForWorkTag true Yes Yes
Insurance Info 10 2 CompanyHealth_FitForWorkTag true Yes Yes
Insurance Info 11 1 CompanyHealth_HealthCareConversionTag true No No
Insurance Info 11 2 CompanyHealth_HealthCareConversionTag true No No
Insurance Info 12 1 CompanyHealth_HealthCareInsuranceTag true Yes Yes
Insurance Info 12 2 CompanyHealth_HealthCareInsuranceTag true Yes Yes
Insurance Info 13 1 CompanyHealth_HealthCarePackageTag true Tier 1 Tier 1
Insurance Info 13 2 CompanyHealth_HealthCarePackageTag true Tier 1 Tier 1
Insurance Info 14 1 CompanyHealth_LifeInsuranceTag true Yes Yes
Insurance Info 14 2 CompanyHealth_LifeInsuranceTag true No No
Insurance Info 15 1 CompanyHealth_LifeProtectionTag true 500 Sterling 500 Sterling
Insurance Info 16 1 CompanyHealth_OverheadCostTag true 20 Sterling 20 Sterling
Insurance Info 17 1 CompanyHealth_SicknessBITag true Yes Yes
Insurance Info 17 2 CompanyHealth_SicknessBITag true No No
Insurance Info 18 1 CompanyHealth_TypeofInsuredTag true Full Full
Insurance Info 18 2 CompanyHealth_TypeofInsuredTag true Full Full
PolicyHolder Info 19 1 CreditScoreTag true NULL NULL
PolicyHolder Info 20 1 CustomerAndInsuredPersonFirstNameTag true Mary Mary
PolicyHolder Info 21 1 CustomerAndInsuredPersonLastNameTag true NULL NULL
PolicyHolder Info 22 1 CustomerAndInsuredPersonSocialSecurityTag true 1111111111 1111111111
Period of Insurance 23 1 EndCoverTag true 2015-12-31 2015-12-31
Other Info 24 1 ExternalTextTag true NULL NULL
Contct Name 25 1 FirstNameTag_1 true NULL NULL
Contct Name 26 1 FirstNameTag_2 true NULL NULL
Insurance Info 27 1 InsuredPersonFirstNameTag true Joanne Joanne
Insurance Info 27 2 InsuredPersonFirstNameTag true Kim Kim
Insurance Info 28 1 InsuredPersonLastNameTag true Smith Smith
Insurance Info 28 2 InsuredPersonLastNameTag true Smith Smith
Insurance Info 29 1 InsuredPersonSocialSecurityTag true 999999999999 999999999999
Insurance Info 29 2 InsuredPersonSocialSecurityTag true 888888888888 888888888888
Other Info 30 1 InternalTextTag true NULL NULL
Contct Name 31 1 LastNameTag_1 true NULL NULL
Contct Name 32 1 LastNameTag_2 true NULL NULL
Period of Insurance 33 1 StartCoverTag true 2015-01-01 2015-01-01
PolicyHolder Info 34 1 StopQuoteTag true NULL NULL
Contct Name 35 1 TelephoneNumberTag_1 true NULL NULL
Contct Name 36 1 TelephoneNumberTag_2 true NULL NULL
Contct Name 37 1 TitleTag_1 true NULL NULL
Contct Name 38 1 TitleTag_2 true NULL NULL
September 23, 2016 at 9:51 am
Hi Eirikur,
Thanks for replying.
I'm actually on my way home.
I'll check when I get in.
I had to anonomise the data manually so I may have messed up something. Both the source and expected results output.
As for the extra header information in the xml. Um odd. I copied the output from what was already in the xml column. Again it might be a copy and paste issue.
Thanks
Rodney.
September 23, 2016 at 1:14 pm
rodjkidd (9/23/2016)
Hi Eirikur,Thanks for replying.
I'm actually on my way home.
I'll check when I get in.
I had to anonomise the data manually so I may have messed up something. Both the source and expected results output.
As for the extra header information in the xml. Um odd. I copied the output from what was already in the xml column. Again it might be a copy and paste issue.
Thanks
Rodney.
No worries mate,
just ping back when ready, happy to help.
😎
September 26, 2016 at 2:47 am
Hi Eirikur,
Thanks for your help. Much appreciated.
Oh and you can ignore the Tag_ID - its the primary key from another table. I shouldn't have included it in the results. Sorry.
Also the original query called it Rank. Its really policy subgroup, I guess you say.
I did think to start with I was getting the same issue. But I now see that its swapped the two policy sub groups around. Which I don't think will be an issue. The CSN isn't as far as I can see important other than to distinguish the sub groups.
I assume this is due to the combination of the query order by and the Row_number order by @@version - which I didn't know about.
I shall now test against other policies - I was only given the one to test against. And I shall let you know the outcome. But its looks promising based on this this test case.
Many thanks,
Rodney
September 27, 2016 at 12:53 am
rodjkidd (9/26/2016)
Row_number order by @@version - which I didn't know about.
I'm just to lazy to write "(SELECT NULL)" when @@VERSION does the same thing, that is tells the server to ignore any specific order and enumerate entries in the order of appearance. In fact one can use any of the @@ functions for this but I've only asserted that the execution plan remains identical with the @@VERSION.
😎
September 27, 2016 at 1:49 am
Eirikur Eiriksson (9/27/2016)
rodjkidd (9/26/2016)
Row_number order by @@version - which I didn't know about.I'm just to lazy to write "(SELECT NULL)" when @@VERSION does the same thing, that is tells the server to ignore any specific order and enumerate entries in the order of appearance. In fact one can use any of the @@ functions for this but I've only asserted that the execution plan remains identical with the @@VERSION.
😎
Funny enough to start with I though oh some clever extra usage of @@version in the Order by...
But by the afternoon I had realised it was instead of (SELECT NULL) 🙂
Thanks for the clarification.
Other than it seems to reverse the order of the subgroupings I think this is working on the bigger data set. I'll pass the query over for further testing. I was only given the one insurance policy that wasn't working. A bit of a habit here I have discovered. So I don't know if there are other policies with issues. If it all goes well I will mark the answer as good. Otherwise I will be back with more questions!
Thanks for your help Eirikur.
Cheers,
Rodney
September 28, 2016 at 2:36 am
Hi Eirikur,
After some more testing its not producing the desired results. I'm in the process of finding a couple of examples and anonymising the xml etc.
Cheers,
Rodney
September 28, 2016 at 4:44 am
OK so using the same test policy I've tried the following;
DECLARE @TXML XML = N'<?xml version="1.0" encoding="utf-16"?>
<AnswersDefinition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Version>
<VersionNumber>100</VersionNumber>
</Version>
<HD key="HiddenRuntimeData">
<Item>
<ArrayOfKeyedItemOfString xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<KeyedItemOfString key="CreditRating">
<Item />
</KeyedItemOfString>
</ArrayOfKeyedItemOfString></Item>
</HD>
<HD key="QuoteInfo">
<Item><QuoteInfo xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><RenewalCount>1</RenewalCount><ProductID xsi:type="xsd:int">400</ProductID><Key>QuoteInfo</Key></QuoteInfo></Item>
</HD>
<HD key="HiddenValueRenewalUpdatePointData">
<Item><RenewalUpdatePointData xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><RenewalUpdatePoints /></RenewalUpdatePointData></Item>
</HD>
<HD key="Rates">
<Item><ArrayOfRate xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><Rate><Type>Tax</Type><Value>32</Value><FrozenOnStartOfCover>true</FrozenOnStartOfCover><Key>Tax</Key></Rate></ArrayOfRate></Item>
</HD>
<PL>
<AP>
<N />
<AL>
<APS>
<T>PolicyHolder Info</T>
<AL>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>1111111111</DV>
</DVL>
<VL>
<V>1111111111</V>
</VL>
<QT>CustomerAndInsuredPersonSocialSecurityTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Mary</DV>
</DVL>
<VL>
<V>Mary</V>
</VL>
<QT>CustomerAndInsuredPersonFirstNameTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL />
<VL />
<QT>CustomerAndInsuredPersonLastNameTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>9999</DV>
</DVL>
<VL>
<V>9999</V>
</VL>
<QT>CampaignCodeTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Domestic</DV>
</DVL>
<VL>
<V>Domestic</V>
</VL>
<QT>AddrLine1Tag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>AA1 1AA</DV>
</DVL>
<VL>
<V>AA1 1AA</V>
</VL>
<QT>AddrPostCodeTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Wessex</DV>
</DVL>
<VL>
<V>Wessex</V>
</VL>
<QT>AddrLine3Tag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL />
<VL />
<QT>CreditScoreTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL />
<VL />
<QT>StopQuoteTag</QT>
</AWT>
</AL>
</APS>
<APS>
<T>Contct Name</T>
<AL>
<AWT>
<VAL>true</VAL>
<DVL />
<VL />
<QT>FirstNameTag_1</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL />
<VL />
<QT>LastNameTag_1</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL />
<VL />
<QT>TelephoneNumberTag_1</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL />
<VL />
<QT>TitleTag_1</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL />
<VL />
<QT>FirstNameTag_2</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL />
<VL />
<QT>LastNameTag_2</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL />
<VL />
<QT>TelephoneNumberTag_2</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL />
<VL />
<QT>TitleTag_2</QT>
</AWT>
</AL>
</APS>
<APS>
<T>Insurance Info</T>
<AL>
<ARSQ>
<VAL>true</VAL>
<QT>CompanyHealth_RepeatingTag</QT>
<NCSN>3</NCSN>
<RSQPL>
<ASQL>
<CSN>1</CSN>
<SQPL>
<AP>
<N />
<AL>
<APS>
<T>Insured Person</T>
<AL>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>999999999999</DV>
</DVL>
<VL>
<V>999999999999</V>
</VL>
<QT>InsuredPersonSocialSecurityTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Joanne</DV>
</DVL>
<VL>
<V>Joanne</V>
</VL>
<QT>InsuredPersonFirstNameTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Smith</DV>
</DVL>
<VL>
<V>Smith</V>
</VL>
<QT>InsuredPersonLastNameTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>1990-01-01</DV>
</DVL>
<VL>
<V>1990-01-01</V>
</VL>
<QT>CompanyHealth_DateOfBirthTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>26</DV>
</DVL>
<VL>
<V>26</V>
</VL>
<QT>CompanyHealth_AgeofInsuredPersonTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Full</DV>
</DVL>
<VL>
<V>Full</V>
</VL>
<QT>CompanyHealth_TypeofInsuredTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Yes</DV>
</DVL>
<VL>
<V>Yes</V>
</VL>
<QT>CompanyHealth_FitForWorkTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Yes</DV>
</DVL>
<VL>
<V>Yes</V>
</VL>
<QT>CompanyHealth_CompanyInsuredWithZZZZZTag</QT>
</AWT>
</AL>
</APS>
<APS>
<T>Insurance Info</T>
<AL>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>No</DV>
</DVL>
<VL>
<V>No</V>
</VL>
<QT>CompanyHealth_LifeInsuranceTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Yes</DV>
</DVL>
<VL>
<V>Yes</V>
</VL>
<QT>CompanyHealth_AccidentalInsuranceTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>1000 Sterling</DV>
</DVL>
<VL>
<V>1000 Sterling</V>
</VL>
<QT>CompanyHealth_AccidentalAmountTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Yes</DV>
</DVL>
<VL>
<V>Yes</V>
</VL>
<QT>CompanyHealth_HealthCareInsuranceTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Tier 1</DV>
</DVL>
<VL>
<V>Tier 1</V>
</VL>
<QT>CompanyHealth_HealthCarePackageTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>No</DV>
</DVL>
<VL>
<V>No</V>
</VL>
<QT>CompanyHealth_HealthCareConversionTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>No</DV>
</DVL>
<VL>
<V>No</V>
</VL>
<QT>CompanyHealth_SicknessBITag</QT>
</AWT>
</AL>
</APS>
</AL>
</AP>
</SQPL>
</ASQL>
<ASQL>
<CSN>2</CSN>
<SQPL>
<AP>
<N />
<AL>
<APS>
<T>Insured Info</T>
<AL>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>888888888888</DV>
</DVL>
<VL>
<V>888888888888</V>
</VL>
<QT>InsuredPersonSocialSecurityTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Kim</DV>
</DVL>
<VL>
<V>Kim</V>
</VL>
<QT>InsuredPersonFirstNameTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Smith</DV>
</DVL>
<VL>
<V>Smith</V>
</VL>
<QT>InsuredPersonLastNameTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>1970-01-01</DV>
</DVL>
<VL>
<V>1970-01-01</V>
</VL>
<QT>CompanyHealth_DateOfBirthTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>46</DV>
</DVL>
<VL>
<V>43</V>
</VL>
<QT>CompanyHealth_AgeofInsuredPersonTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Full</DV>
</DVL>
<VL>
<V>Full</V>
</VL>
<QT>CompanyHealth_TypeofInsuredTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Yes</DV>
</DVL>
<VL>
<V>Yes</V>
</VL>
<QT>CompanyHealth_FitForWorkTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Yes</DV>
</DVL>
<VL>
<V>Yes</V>
</VL>
<QT>CompanyHealth_CompanyInsuredWithZZZZZTag</QT>
</AWT>
</AL>
</APS>
<APS>
<T>Insured Info</T>
<AL>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Yes</DV>
</DVL>
<VL>
<V>Yes</V>
</VL>
<QT>CompanyHealth_LifeInsuranceTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>500 Sterling</DV>
</DVL>
<VL>
<V>500 Sterling</V>
</VL>
<QT>CompanyHealth_LifeProtectionTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Yes</DV>
</DVL>
<VL>
<V>Yes</V>
</VL>
<QT>CompanyHealth_AccidentalInsuranceTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>1000 Sterling</DV>
</DVL>
<VL>
<V>1000 Sterling</V>
</VL>
<QT>CompanyHealth_AccidentalAmountTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Yes</DV>
</DVL>
<VL>
<V>Yes</V>
</VL>
<QT>CompanyHealth_HealthCareInsuranceTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Tier 1</DV>
</DVL>
<VL>
<V>Tier 1</V>
</VL>
<QT>CompanyHealth_HealthCarePackageTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>No</DV>
</DVL>
<VL>
<V>No</V>
</VL>
<QT>CompanyHealth_HealthCareConversionTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>Yes</DV>
</DVL>
<VL>
<V>Yes</V>
</VL>
<QT>CompanyHealth_SicknessBITag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>20 Sterling</DV>
</DVL>
<VL>
<V>20 Sterling</V>
</VL>
<QT>CompanyHealth_OverheadCostTag</QT>
</AWT>
</AL>
</APS>
</AL>
</AP>
</SQPL>
</ASQL>
</RSQPL>
</ARSQ>
</AL>
</APS>
<APS>
<T>Other Info</T>
<AL>
<AWT>
<VAL>true</VAL>
<DVL />
<VL />
<QT>ExternalTextTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL />
<VL />
<QT>InternalTextTag</QT>
</AWT>
</AL>
</APS>
<APS>
<T>Period of Insurance</T>
<AL>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>2015-01-01</DV>
</DVL>
<VL>
<V>2015-01-01</V>
</VL>
<QT>StartCoverTag</QT>
</AWT>
<AWT>
<VAL>true</VAL>
<DVL>
<DV>2015-12-31</DV>
</DVL>
<VL>
<V>2015-12-31</V>
</VL>
<QT>EndCoverTag</QT>
</AWT>
</AL>
</APS>
</AL>
</AP>
</PL>
</AnswersDefinition>';
SELECT
ANSWDF.DATA.value('(T/text())[1]','VARCHAR(100)') AS NODE_NAME
,ANSWDF.DATA.value('(AL/ARSQ/RSQPL/ASQL/CSN/text())[1]','INT') AS CSN_VAL
,AWT.DATA.value('(QT/text())[1]' ,'VARCHAR(100)') AS TAG_NAME
,AWT.DATA.value('(DVL/DV/text())[1]','VARCHAR(100)') AS TAG_DISP_VALUE
,AWT.DATA.value('(VL/V/text())[1]' ,'VARCHAR(100)') AS TAG_VALUE
FROM @TXML.nodes('AnswersDefinition/PL/AP/AL/APS') ANSWDF(DATA)
CROSS APPLY ANSWDF.DATA.nodes('AL//AWT') AWT(DATA);
So I get the 53 rows
But
I get NULL for the CSN for the tags that aren't repeating - not a problem as I can deal with that as a second step.
The real problem is the rows for the two insured persons both get a CSN grouping of 1. There should be 1 and 2.
So I tried this variation, I've only included the query not the xml variable;
SELECT
ANSWDF.DATA.value('(T/text())[1]','VARCHAR(100)') AS NODE_NAME
,CSN.DATA.value('(CSN/text())[1]', 'INT') AS CSN_NUMBER
,AWT.DATA.value('(QT/text())[1]' ,'VARCHAR(100)') AS TAG_NAME
,AWT.DATA.value('(DVL/DV/text())[1]','VARCHAR(100)') AS TAG_DISP_VALUE
,AWT.DATA.value('(VL/V/text())[1]' ,'VARCHAR(100)') AS TAG_VALUE
FROM @TXML.nodes('AnswersDefinition/PL/AP/AL/APS') ANSWDF(DATA)
CROSS APPLY ANSWDF.DATA.nodes('AL//AWT') AWT(DATA)
CROSS APPLY ANSWDF.DATA.nodes('AL//ASQL') CSN(DATA);
But I then get 64 rows, as the non repeating tags get repeated for each CSN number.
I have found one policy with 13 people on it, some with additional extras, which if needed I can anonymise. I'm not sure using ROW_NUMBER() is going to help as you don't get the same additional extra tags per person.
Any ideas how to get 53 rows, with two groups numbered 1 and 2? Or three with the "header" rows having NULL for CSN?
Regards,
Rodney
September 28, 2016 at 4:56 am
Hi Rodney,
this should be easy to fix, will have a look later today
😎
September 28, 2016 at 5:03 am
Many thanks.
Let me know if you want the 13 person one uploaded. As the additional extras are all over the place in that one! But going to take a while to anonymise!
Rodney
September 28, 2016 at 11:17 pm
rodjkidd (9/28/2016)
Many thanks.Let me know if you want the 13 person one uploaded. As the additional extras are all over the place in that one! But going to take a while to anonymise!
Rodney
Here is a possible solution which handles the "Insured Person" node in a separate query, you should run the 13 person one and get 16 rows p. person in addition to the 21 rows in the header.
😎
SELECT
ANSWDF.DATA.value('(T/text())[1]','VARCHAR(100)') AS NODE_NAME
,ANSWDF.DATA.value('(AL/ARSQ/RSQPL/ASQL/CSN/text())[1]','INT')AS CSN_VAL
,AWT.DATA.value('(QT/text())[1]' ,'VARCHAR(100)') AS TAG_NAME
,AWT.DATA.value('(DVL/DV/text())[1]','VARCHAR(100)') AS TAG_DISP_VALUE
,AWT.DATA.value('(VL/V/text())[1]' ,'VARCHAR(100)') AS TAG_VALUE
FROM @TXML.nodes('AnswersDefinition/PL/AP/AL/APS') ANSWDF(DATA)
OUTER APPLY ANSWDF.DATA.nodes('AL/AWT') AWT(DATA)
WHERE ANSWDF.DATA.value('(T/text())[1]','VARCHAR(100)') IN (
'PolicyHolder Info'
,'Other Info'
,'Period of Insurance'
,'Contct Name'
)
UNION ALL
SELECT
ANSWDF.DATA.value('(T/text())[1]','VARCHAR(100)') AS NODE_NAME
,ARSQ.DATA.value('(CSN/text())[1]','INT') AS CSN_VAL
,APS.DATA.value('(../../T/text())[1]' ,'VARCHAR(100)') AS TAG_NAME
,APS.DATA.value('(DVL/DV/text())[1]','VARCHAR(100)') AS TAG_DISP_VALUE
,APS.DATA.value('(VL/V/text())[1]' ,'VARCHAR(100)') AS TAG_VALUE
FROM @TXML.nodes('AnswersDefinition/PL/AP/AL/APS') ANSWDF(DATA)
CROSS APPLY ANSWDF.DATA.nodes('AL/ARSQ/RSQPL/ASQL') ARSQ(DATA)
CROSS APPLY ARSQ.DATA.nodes('SQPL/AP/AL/APS/AL/AWT') APS(DATA);
Output
NODE_NAME CSN_VAL TAG_NAME TAG_DISP_VALUE TAG_VALUE
--------------------- ----------- ------------------------------------------ --------------- ----------------
PolicyHolder Info NULL CustomerAndInsuredPersonSocialSecurityTag 1111111111 1111111111
PolicyHolder Info NULL CustomerAndInsuredPersonFirstNameTag Mary Mary
PolicyHolder Info NULL CustomerAndInsuredPersonLastNameTag NULL NULL
PolicyHolder Info NULL CampaignCodeTag 9999 9999
PolicyHolder Info NULL AddrLine1Tag Domestic Domestic
PolicyHolder Info NULL AddrPostCodeTag AA1 1AA AA1 1AA
PolicyHolder Info NULL AddrLine3Tag Wessex Wessex
PolicyHolder Info NULL CreditScoreTag NULL NULL
PolicyHolder Info NULL StopQuoteTag NULL NULL
Contct Name NULL FirstNameTag_1 NULL NULL
Contct Name NULL LastNameTag_1 NULL NULL
Contct Name NULL TelephoneNumberTag_1 NULL NULL
Contct Name NULL TitleTag_1 NULL NULL
Contct Name NULL FirstNameTag_2 NULL NULL
Contct Name NULL LastNameTag_2 NULL NULL
Contct Name NULL TelephoneNumberTag_2 NULL NULL
Contct Name NULL TitleTag_2 NULL NULL
Other Info NULL ExternalTextTag NULL NULL
Other Info NULL InternalTextTag NULL NULL
Period of Insurance NULL StartCoverTag 2015-01-01 2015-01-01
Period of Insurance NULL EndCoverTag 2015-12-31 2015-12-31
Insurance Info 1 Insured Person 999999999999 999999999999
Insurance Info 1 Insured Person Joanne Joanne
Insurance Info 1 Insured Person Smith Smith
Insurance Info 1 Insured Person 1990-01-01 1990-01-01
Insurance Info 1 Insured Person 26 26
Insurance Info 1 Insured Person Full Full
Insurance Info 1 Insured Person Yes Yes
Insurance Info 1 Insured Person Yes Yes
Insurance Info 1 Insurance Info No No
Insurance Info 1 Insurance Info Yes Yes
Insurance Info 1 Insurance Info 1000 Sterling 1000 Sterling
Insurance Info 1 Insurance Info Yes Yes
Insurance Info 1 Insurance Info Tier 1 Tier 1
Insurance Info 1 Insurance Info No No
Insurance Info 1 Insurance Info No No
Insurance Info 2 Insured Info 888888888888 888888888888
Insurance Info 2 Insured Info Kim Kim
Insurance Info 2 Insured Info Smith Smith
Insurance Info 2 Insured Info 1970-01-01 1970-01-01
Insurance Info 2 Insured Info 46 43
Insurance Info 2 Insured Info Full Full
Insurance Info 2 Insured Info Yes Yes
Insurance Info 2 Insured Info Yes Yes
Insurance Info 2 Insured Info Yes Yes
Insurance Info 2 Insured Info 500 Sterling 500 Sterling
Insurance Info 2 Insured Info Yes Yes
Insurance Info 2 Insured Info 1000 Sterling 1000 Sterling
Insurance Info 2 Insured Info Yes Yes
Insurance Info 2 Insured Info Tier 1 Tier 1
Insurance Info 2 Insured Info No No
Insurance Info 2 Insured Info Yes Yes
Insurance Info 2 Insured Info 20 Sterling 20 Sterling
September 29, 2016 at 2:14 am
Hi Eirikur,
I think I owe you a large drink next time I see you!
I had started working on the basis yesterday that you had to split the query in two - I had the one for the "header" information, but couldn't get the "repeating detail" to work. I either had 2, 23, or 64 rows. As there were 21 in the "header" I was aiming for 32! Oops
You second query certainly only produces the 32 rows. The tag is coming through incorrectly on the real data, but that's an easy fix. I will then test against the 13 person record.
Many thanks,
Rodney
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply