October 3, 2022 at 7:31 am
Hi everyone,
So I have been working on converting my SQL tables data into XML files.
I have designed a query that looks like this:
--START OF TABLE List
SELECT
(
SELECT
--(SELECT COALESCE (List_ID,'')) AS List_ID,
(SELECT COALESCE (List_Type,'')) AS List_Type,
--(SELECT COALESCE (List_Description,'')) AS List_Description,
--(SELECT COALESCE (List_Provider,'')) AS List_Provider,
--(SELECT COALESCE (Issuing_Body,'')) AS Issuing_Body,
--START OF TABLE Individual
(
SELECT
--(SELECT COALESCE (UniqueID,'')) AS UniqueID,
(SELECT COALESCE (Record_ID,'')) AS Record_ID,
(SELECT COALESCE (Record_Type,'')) AS Record_Type,
(SELECT COALESCE (Title,'')) AS Title,
(SELECT COALESCE (Alternate_Title,'')) AS Alternate_Title,
(SELECT COALESCE (Forename,'')) AS Forename,
(SELECT COALESCE (Middlename,'')) AS Middlename,
(SELECT COALESCE (Surname,'')) AS Surname,
(SELECT COALESCE (Full_Name,'')) AS Full_Name,
(SELECT COALESCE (Gender,'')) AS Gender,
--(SELECT COALESCE (Nationality,'')) AS Nationality,
(SELECT COALESCE (Source,'')) AS Source,
--(SELECT COALESCE (Date_of_Capture,'')) AS Date_of_Capture,
(SELECT COALESCE (Soft_Delete,'')) AS Soft_Delete,
(SELECT COALESCE (Date_of_Soft_Delete,'')) AS Date_of_Soft_Delete,
--(SELECT COALESCE (Catefor xml path ry,'')) AS Catefor xml path ry,
--(SELECT COALESCE (Sub_Catefor xml path ry,'')) AS Sub_Catefor xml path ry,
(SELECT COALESCE (Listed_On,'')) AS Listed_On,
(SELECT COALESCE (Created_On,'')) AS Created_On,
(SELECT COALESCE (Modified_On,'')) AS Modified_On,
--(SELECT COALESCE (Individual_List_ID_FK,'')) AS Individual_List_ID_FK,
--(SELECT COALESCE (File_Names,'')) AS File_Names,
--(SELECT COALESCE (Individual_Id,'')) AS Individual_Id,
--(SELECT COALESCE (Ind_PK_Value_Updated,'')) AS Ind_PK_Value_Updated,
--START OF TABLE National_Identity
(
SELECT
--(SELECT COALESCE (NatId_PK,'')) AS NatId_PK,
(SELECT COALESCE (NatID_Type,'')) AS NatID_Type
--,(SELECT COALESCE (NatID_Num,'')) AS NatID_Num,
--,(SELECT COALESCE (NatID_issue,'')) AS NatID_issue,
--,(SELECT COALESCE (NatID_expiry,'')) AS NatID_expiry,
--,(SELECT COALESCE (NatID_State_Province,'')) AS NatID_State_Province,
--,(SELECT COALESCE (NatID_Country,'')) AS NatID_Country,
--,(SELECT COALESCE (isNationality,'')) AS isNationality,
--,(SELECT COALESCE (Soft_Delete,'')) AS Soft_Delete,
--,(SELECT COALESCE (Date_of_Soft_Delete,'')) AS Date_of_Soft_Delete,
--,(SELECT COALESCE (NatId_Record_ID_FK,'')) AS NatId_Record_ID_FK,
--,(SELECT COALESCE (NatId_UniqueID_FK,'')) AS NatId_UniqueID_FK,
--,(SELECT COALESCE (File_Names,'')) AS File_Names,
--,(SELECT COALESCE (National_Identities_Id,'')) AS National_Identities_Id
FROM National_Identity
--where NatId_Record_ID_FK=Record_ID
where NatId_UniqueID_FK=UniqueID
FOR XML PATH('National_Identity'),
ROOT('National_Identitities') ,
elements xsinil,
type) as [*],
--END OF TABLE National_Identity
--START OF TABLE Birth_Detail
(
SELECT
--(SELECT COALESCE (Birth_ID,'')) AS Birth_ID,
(SELECT COALESCE (Date_of_Birth,'')) AS Date_of_Birth,
(SELECT COALESCE (Year_of_Birth,'')) AS Year_of_Birth,
(SELECT COALESCE (Place_of_Birth,'')) AS Place_of_Birth
--,(SELECT COALESCE (Soft_Delete,'')) AS Soft_Delete
--,(SELECT COALESCE (Date_of_Soft_Delete,'')) AS Date_of_Soft_Delete
--,(SELECT COALESCE (Birth_Details_Record_ID_FK,'')) AS Birth_Details_Record_ID_FK
--,(SELECT COALESCE (Birth_Details_UniqueID_FK,'')) AS Birth_Details_UniqueID_FK
--,(SELECT COALESCE (File_Names,'')) AS File_Names
--,(SELECT COALESCE (Birth_Details_Id,'')) AS Birth_Details_Id
FROM Birth_Detail
--where Birth_Details_Record_ID_FK = Record_ID
where Birth_Details_UniqueID_FK = UniqueID
for xml path('Birth_Detail'), root('Birth_Details'),
elements xsinil,
type) as [*],
--END OF TABLE Birth_Detail
--START OF TABLE Death_Detail
(
SELECT
--(SELECT COALESCE (Death_ID,'')) AS Death_ID,
(SELECT COALESCE (Date_of_Death,'')) AS Date_of_Death,
(SELECT COALESCE (Year_of_Death,'')) AS Year_of_Death,
(SELECT COALESCE (Place_of_Death,'')) AS Place_of_Death
--,(SELECT COALESCE (Soft_Delete,'')) AS Soft_Delete
--,(SELECT COALESCE (Date_of_Soft_Delete,'')) AS Date_of_Soft_Delete
--,(SELECT COALESCE (Death_Details_Record_ID_FK,'')) AS Death_Details_Record_ID_FK
--,(SELECT COALESCE (Death_Details_UniqueID_FK,'')) AS Death_Details_UniqueID_FK
--,(SELECT COALESCE (File_Names,'')) AS File_Names
--,(SELECT COALESCE (Death_Details_Id,'')) AS Death_Details_Id
FROM Death_Detail
--where Death_Details_Record_ID_FK = Record_ID
where Death_Details_UniqueID_FK = UniqueID
for xml path('Death_Detail'), root('Death_Details'),
elements xsinil,
type) as [*],
--END OF TABLE Death_Detail
--START OF TABLE Contact_Info
(
SELECT
--(SELECT COALESCE (ConInfo_ID,'')) AS ConInfo_ID,
(SELECT COALESCE (Home_Telephone,'')) AS Home_Telephone,
(SELECT COALESCE (Business_Telephone,'')) AS Business_Telephone,
(SELECT COALESCE (Mobile_Telephone,'')) AS Mobile_Telephone,
(SELECT COALESCE (Fax,'')) AS Fax,
(SELECT COALESCE (Email,'')) AS Email
--,(SELECT COALESCE (Soft_Delete,'')) AS Soft_Delete
--,(SELECT COALESCE (Date_of_Soft_Delete,'')) AS Date_of_Soft_Delete
--,(SELECT COALESCE (ConInfo_Record_ID_FK,'')) AS ConInfo_Record_ID_FK
--,(SELECT COALESCE (ConInfo_UniqueID_FK,'')) AS ConInfo_UniqueID_FK
--,(SELECT COALESCE (File_Names,'')) AS File_Names
--,(SELECT COALESCE (Contact_Infos_Id,'')) AS Contact_Infos_Id
FROM Contact_Info
--where ConInfo_Record_ID_FK = Record_ID
where ConInfo_UniqueID_FK = UniqueID
for xml path('Contact_Info'), root('Contact_Infos'),
elements xsinil,
type) as [*],
--END OF TABLE Contact_Info
--START OF TABLE Photo_Identity
(
SELECT
--(SELECT COALESCE (PIden_ID,'')) AS PIden_ID,
(SELECT COALESCE (Photo,'')) AS Photo,
--,(SELECT COALESCE (File_Name1,'')) AS File_Name1
(SELECT COALESCE (URL_Photo,'')) AS URL_Photo
--,(SELECT COALESCE (Date_of_Capture,'')) AS Date_of_Capture
--,(SELECT COALESCE (Soft_Delete,'')) AS Soft_Delete
--,(SELECT COALESCE (Date_of_Soft_Delete,'')) AS Date_of_Soft_Delete
--,(SELECT COALESCE (PIden_Record_ID_FK,'')) AS PIden_Record_ID_FK
--,(SELECT COALESCE (PIden_UniqueID_FK,'')) AS PIden_UniqueID_FK
--,(SELECT COALESCE (File_Names,'')) AS File_Names
--,(SELECT COALESCE (Photo_Identities_Id,'')) AS Photo_Identities_Id
FROM Photo_Identity
--where PIden_Record_ID_FK= Record_ID
where PIden_UniqueID_FK= UniqueID
for xml path('Photo_Identity'), root('Photo_Identities'),
elements xsinil,
type) as [*],
--END OF TABLE Photo_Identity
--START OF TABLE Publishing_Detail
(
SELECT
--(SELECT COALESCE (PubDet_ID,'')) AS PubDet_ID,
(SELECT COALESCE (FullAccessURL,'')) AS FullAccessURL,
(SELECT COALESCE (LimitedAccessURL,'')) AS LimitedAccessURL
--,(SELECT COALESCE (Soft_Delete,'')) AS Soft_Delete
--,(SELECT COALESCE (Date_of_Soft_Delete,'')) AS Date_of_Soft_Delete
--,(SELECT COALESCE (PubDet_Record_ID_FK,'')) AS PubDet_Record_ID_FK
--,(SELECT COALESCE (PubDet_UniqueID_FK,'')) AS PubDet_UniqueID_FK
--,(SELECT COALESCE (File_Names,'')) AS File_Names
--,(SELECT COALESCE (Publishing_Details_Id,'')) AS Publishing_Details_Id
FROM Publishing_Detail
--where PubDet_Record_ID_FK= Record_ID
where PubDet_UniqueID_FK= UniqueID
for xml path('Publishing_Detail'), root('Publishing_Details'),
elements xsinil,
type) as [*],
--END OF TABLE Publishing_Detail
--START OF TABLE Addresses
(
SELECT
--(SELECT COALESCE (Addresses_Id,'')) AS Addresses_Id
--,(SELECT COALESCE (Individual_Id,'')) AS Individual_Id
--,(SELECT COALESCE (Total,'')) AS Total
--,(SELECT COALESCE (Adds_PK_Value_Updated,'')) AS Adds_PK_Value_Updated,
--START OF TABLE Address
(
SELECT
--(SELECT COALESCE (Add_Id,'')) AS Add_Id,
(SELECT COALESCE (Address_Line_1,'')) AS Address_Line_1,
(SELECT COALESCE (Address_Line_2,'')) AS Address_Line_2,
(SELECT COALESCE (Address_Line_3,'')) AS Address_Line_3,
(SELECT COALESCE (Address_Line_4,'')) AS Address_Line_4,
(SELECT COALESCE (Town_District,'')) AS Town_District,
(SELECT COALESCE (City,'')) AS City,
(SELECT COALESCE (County_State,'')) AS County_State,
(SELECT COALESCE (Post_Zip_Code,'')) AS Post_Zip_Code,
(SELECT COALESCE (Country,'')) AS Country,
(SELECT COALESCE (ISO_Country,'')) AS ISO_Country
--,(SELECT COALESCE (Soft_Delete,'')) AS Soft_Delete
--,(SELECT COALESCE (Date_of_Soft_Delete,'')) AS Date_of_Soft_Delete
--,(SELECT COALESCE (Address_Record_ID_FK,'')) AS Address_Record_ID_FK
--,(SELECT COALESCE (Address_UNIQUEID_FK,'')) AS Address_UNIQUEID_FK
--,(SELECT COALESCE (File_Names,'')) AS File_Names
--,(SELECT COALESCE (Addresses_Id,'')) AS Addresses_Id
FROM Address a2
where
a2.Addresses_Id = a1.Addresses_Id
for xml path('Address'),
elements xsinil,
type) as [*]
--END OF TABLE Address
FROM Addresses a1
where
a1.Individual_Id = ind.Individual_Id
for xml path('Addresses'),
elements xsinil,
type) as [*],
--END OF TABLE Addresses
--START OF TABLE Aliases
(
SELECT
--(SELECT COALESCE (Aliases_Id,'')) AS Aliases_Id
--,(SELECT COALESCE (Individual_Id,'')) AS Individual_Id
--,(SELECT COALESCE (Total,'')) AS Total
--,(SELECT COALESCE (Als_PK_Value_Updated,'')) AS Als_PK_Value_Updated,
--START OF TABLE Alias
(
SELECT
(SELECT COALESCE (AliasID,'')) AS AliasID,
(SELECT COALESCE (Title,'')) AS Title,
(SELECT COALESCE (Alternate_Title,'')) AS Alternate_Title,
(SELECT COALESCE (Forename,'')) AS Forename,
(SELECT COALESCE (Middle_Name,'')) AS Middle_Name,
(SELECT COALESCE (Surname,'')) AS Surname,
(SELECT COALESCE (Full_Alias,'')) AS Full_Alias
--(SELECT COALESCE (Soft_Delete,'')) AS Soft_Delete
--(SELECT COALESCE (Date_of_Soft_Delete,'')) AS Date_of_Soft_Delete
--,(SELECT COALESCE (Alias_Record_ID_FK,'')) AS Alias_Record_ID_FK
--,(SELECT COALESCE (Alias_UniqueID_FK,'')) AS Alias_UniqueID_FK
--,(SELECT COALESCE (File_Names,'')) AS File_Names
--,(SELECT COALESCE (Aliases_Id,'')) AS Aliases_Id
FROM Alias a2
where
a2.Aliases_Id = a1.Aliases_Id
for xml path('Alias'),
elements xsinil,
type) as [*]
--END OF TABLE Alias
FROM Aliases a1
where
a1.Individual_Id = ind.Individual_Id
for xml path('Aliases'),
elements xsinil,
type) as [*],
--END OF TABLE Aliases
--START OF TABLE Notes
(
SELECT
--(SELECT COALESCE (Notes_Id,'')) AS Notes_Id
--,(SELECT COALESCE (Individual_Id,'')) AS Individual_Id
--,(SELECT COALESCE (Total,'')) AS Total
--,(SELECT COALESCE (Notes_PK_Value_Updated,'')) AS Notes_PK_Value_Updated,
--START OF TABLE Note
(SELECT
--(SELECT COALESCE (Note_Id,'')) AS Note_Id,
(SELECT COALESCE (Source_of_Notes,'')) AS Source_of_Notes,
(SELECT COALESCE (Notes,'')) AS Notes
--(SELECT COALESCE (Soft_Delete,'')) AS Soft_Delete
--,(SELECT COALESCE (Date_of_Soft_Delete,'')) AS Date_of_Soft_Delete
--,(SELECT COALESCE (Note_Record_ID_FK,'')) AS Note_Record_ID_FK
--,(SELECT COALESCE (Note_UniqueID_FK,'')) AS Note_UniqueID_FK
--,(SELECT COALESCE (File_Names,'')) AS File_Names
--,(SELECT COALESCE (Notes_Id,'')) AS Notes_Id
FROM Note a2
where
a2.Notes_Id = a1.Notes_Id
for xml path('Note'),
elements xsinil,
type) as [*]
--END OF TABLE Note
FROM Notes a1
where
a1.Individual_Id = ind.Individual_Id
for xml path('Notes'),
elements xsinil,
type) as [*],
--END OF TABLE Notes
--START OF TABLE Political_Positions
(
SELECT
--(SELECT COALESCE (Political_Position,'')) AS Political_Position
--,(SELECT COALESCE (Individual_Id,'')) AS Individual_Id
--,(SELECT COALESCE (Total,'')) AS Total
--,(SELECT COALESCE (PPs_PK_Value_Updated,'')) AS PPs_PK_Value_Updated,
--START OF TABLE Political_Position
(SELECT
--(SELECT COALESCE (PolPos_ID,'')) AS PolPos_ID,
(SELECT COALESCE (Description,'')) AS Description,
(SELECT COALESCE ([From],'')) AS [From],
(SELECT COALESCE ([To],'')) AS [To],
(SELECT COALESCE (Country,'')) AS Country,
--,(SELECT COALESCE (Soft_Delete,'')) AS Soft_Delete
--,(SELECT COALESCE (Date_of_Soft_Delete,'')) AS Date_of_Soft_Delete
--,(SELECT COALESCE (PolPos_Record_ID_FK,'')) AS PolPos_Record_ID_FK
--,(SELECT COALESCE (PolPos_UniqueID_FK,'')) AS PolPos_UniqueID_FK
(SELECT COALESCE (PEP_Tier,'')) AS PEP_Tier,
(SELECT COALESCE (Designation,'')) AS Designation,
(SELECT COALESCE (Institution,'')) AS Institution,
(SELECT COALESCE (Risk_Category,'')) AS Risk_Category
--(SELECT COALESCE (File_Names,'')) AS File_Names
--,(SELECT COALESCE (Political_Positions_Id,'')) AS Political_Positions_Id
FROM Political_Position a2
where
a2.Political_Positions_Id = a1.Political_Positions_Id
for xml path('Political_Position'),
elements xsinil,
type) as [*]
--END OF TABLE Political_Position
FROM Political_Positions a1
where
a1.Individual_Id = ind.Individual_Id
for xml path('Political_Positions'),
elements xsinil,
type) as [*],
--END OF TABLE Political_Positions
--START OF TABLE Sanctions
(
SELECT
--(SELECT COALESCE (Sanctions_Id,'')) AS Sanctions_Id
--,(SELECT COALESCE (Individual_Id,'')) AS Individual_Id
--,(SELECT COALESCE (Total,'')) AS Total
--,(SELECT COALESCE (Sans_PK_Value_Updated,'')) AS Sans_PK_Value_Updated,
--START OF TABLE Sanction
(SELECT
--(SELECT COALESCE (San_Id,'')) AS San_Id,
(SELECT COALESCE (Sanction_Body,'')) AS Sanction_Body,
(SELECT COALESCE (Sanction_Is_Current,'')) AS Sanction_Is_Current
--,(SELECT COALESCE (Soft_Delete,'')) AS Soft_Delete
--,(SELECT COALESCE (Date_of_Soft_Delete,'')) AS Date_of_Soft_Delete
--,(SELECT COALESCE (Sanction_Record_ID_FK,'')) AS Sanction_Record_ID_FK
--,(SELECT COALESCE (Sanction_UniqueID_FK,'')) AS Sanction_UniqueID_FK
--,(SELECT COALESCE (File_Names,'')) AS File_Names
--,(SELECT COALESCE (Sanctions_Id,'')) AS Sanctions_Id
FROM Sanction a2
where
a2.Sanctions_Id = a1.Sanctions_Id
for xml path('Sanction'),
elements xsinil,
type) as [*]
--END OF TABLE Sanction
FROM Sanctions a1
where
a1.Individual_Id = ind.Individual_Id
for xml path('Sanctions'),
elements xsinil,
type) as [*],
--END OF TABLE Sanctions
--START OF TABLE Individual_Associations
(
SELECT
--(SELECT COALESCE (Individual_Associations_Id,'')) AS Individual_Associations_Id
--,(SELECT COALESCE (Individual_Id,'')) AS Individual_Id
--,(SELECT COALESCE (Total,'')) AS Total
--,(SELECT COALESCE (IA_PK_Value_Updated,'')) AS IA_PK_Value_Updated,
--START OF TABLE Individual_Association
(SELECT
--(SELECT COALESCE (IA_Id_PK,'')) AS IA_Id_PK,
(SELECT COALESCE (Individual_ID_2,'')) AS Individual_ID_2,
(SELECT COALESCE (Description_of_Relationship,'')) AS Description_of_Relationship
--(SELECT COALESCE (Soft_Delete,'')) AS Soft_Delete
--,(SELECT COALESCE (Date_of_Soft_Delete,'')) AS Date_of_Soft_Delete
--,(SELECT COALESCE (IndAssoc_Record_ID_FK,'')) AS IndAssoc_Record_ID_FK
--,(SELECT COALESCE (IndAssoc_UniqueID_FK,'')) AS IndAssoc_UniqueID_FK
--,(SELECT COALESCE (File_Names,'')) AS File_Names
--,(SELECT COALESCE (Individual_Associations_Id,'')) AS Individual_Associations_Id
FROM Individual_Association a2
where
a2.Individual_Associations_Id = a1.Individual_Associations_Id
for xml path('Individual_Association'),
elements xsinil,
type) as [*]
--END OF TABLE Individual_Association
FROM Individual_Associations a1
where
a1.Individual_Id = ind.Individual_Id
for xml path('Individual_Associations'),
elements xsinil,
type) as [*],
--END OF TABLE Individual_Associations
--START OF TABLE Business_Associations
(
SELECT
--(SELECT COALESCE (Business_Associations_Id,'')) AS Business_Associations_Id
--,(SELECT COALESCE (Individual_Id,'')) AS Individual_Id
--,(SELECT COALESCE (Total,'')) AS Total
--,(SELECT COALESCE (BA_PK_Value_Updated,'')) AS BA_PK_Value_Updated,
--START OF TABLE Business_Association
(
SELECT
--(SELECT COALESCE (BusID_PK,'')) AS BusID_PK
--,
(SELECT COALESCE (Business_ID,'')) AS Business_ID,
(SELECT COALESCE (Business_Name,'')) AS Business_Name,
(SELECT COALESCE (Short_Name,'')) AS Short_Name,
(SELECT COALESCE (Registered_At,'')) AS Registered_At,
(SELECT COALESCE (Registration_Number,'')) AS Registration_Number,
(SELECT COALESCE (Date_of_Incorportion,'')) AS Date_of_Incorportion,
(SELECT COALESCE (Country,'')) AS Country,
(SELECT COALESCE (City,'')) AS City,
(SELECT COALESCE (State,'')) AS State,
(SELECT COALESCE (Province,'')) AS Province,
(SELECT COALESCE (Description,'')) AS Description
--, (SELECT COALESCE (Soft_Delete,'')) AS Soft_Delete
--, (SELECT COALESCE (Date_of_Soft_Delete,'')) AS Date_of_Soft_Delete
--, (SELECT COALESCE (File_Names,'')) AS File_Names
--, (SELECT COALESCE (BusAssoc_Record_ID_FK,'')) AS BusAssoc_Record_ID_FK
--, (SELECT COALESCE (BusAssoc_UniqueID_FK,'')) AS BusAssoc_UniqueID_FK
--, (SELECT COALESCE (Business_Associations_Id,'')) AS Business_Associations_Id
FROM Business_Association a2
where
a2.Business_Associations_Id = a1.Business_Associations_Id
for xml path('Business_Association'),
elements xsinil,
type) as [*]
--END OF TABLE Business_Association
FROM Business_Associations a1
where
a1.Individual_Id = ind.Individual_Id
for xml path('Business_Associations'),
elements xsinil,
type) as [*],
--END OF TABLE Business_Associations
--START OF ARTICLES-->ARTICLE-->SNIPPETS-->SNIPPET
--START OF TABLE Articles (COLUMNS ONLY)
(
SELECT
--(SELECT COALESCE (Articles_Id,'')) AS Articles_Id,
--(SELECT COALESCE (Individual_Id,'')) AS Individual_Id,
--(SELECT COALESCE (Total,'')) AS Total,
--(SELECT COALESCE (Arts_PK_Value_Updated,'')) AS Arts_PK_Value_Updated,
--END OF TABLE Articles (COLUMNS ONLY)
--START OF TABLE Article (COLUMNS ONLY)
(
SELECT
--(SELECT COALESCE (ArtID_Count,'')) AS ArtID_Count,
(SELECT COALESCE (Art_Id,'')) AS Art_Id,
(SELECT COALESCE (Original_URL,'')) AS Original_URL,
(SELECT COALESCE (C6_URL,'')) AS C6_URL,
(SELECT COALESCE (File_Name,'')) AS File_Name,
(SELECT COALESCE (Source,'')) AS Source,
--START OF TABLE SNIPPETS (COLUMNS ONLY)
(SELECT
--(SELECT COALESCE (Snippets_Id,'')) AS Snippets_Id,
--(SELECT COALESCE (Individual_Id,'')) AS Individual_Id,
--(SELECT COALESCE (Total,'')) AS Total,
--(SELECT COALESCE (Snips_PK_Value_Updated,'')) AS Snips_PK_Value_Updated,
--END OF TABLE SNIPPETS (COLUMNS ONLY)
--START OF TABLE SNIPPET (COLUMNS ONLY)
(SELECT
--(SELECT COALESCE (Snip_Id,'')) AS Snip_Id,
(SELECT COALESCE (Title,'')) AS Title,
(SELECT COALESCE (SnippetText,'')) AS SnippetText,
(SELECT COALESCE (Adverse_Terms,'')) AS Adverse_Terms
--,(SELECT COALESCE (Soft_Delete,'')) AS Soft_Delete
--,(SELECT COALESCE (Date_of_Soft_Delete,'')) AS Date_of_Soft_Delete
--,(SELECT COALESCE (File_Names,'')) AS File_Names
--,(SELECT COALESCE (Snippet_Record_ID_FK,'')) AS Snippet_Record_ID_FK
--,(SELECT COALESCE (Snippet_UniqueID_FK,'')) AS Snippet_UniqueID_FK
--,(SELECT COALESCE (Snippets_Id,'')) AS Snippets_Id
--END OF TABLE SNIPPET (COLUMNS ONLY)
--START OF TABLE Snippet (CONDITION SECTION ONLY)
FROM Snippet
WHERE Snippet.Snippets_Id=Snippets.Snippets_id
FOR XML PATH('Snippet'),
ELEMENTS XSINIL,
TYPE) as [*]
--END OF TABLE Snippet (CONDITION SECTION ONLY)
--START OF TABLE Snippets (CONDITION SECTION ONLY)
FROM
--NESTED SUBQUERY CROSS JOIN
(
SELECT
DISTINCT Snippets.Snippets_Id
FROM Snippets, Snippet
WHERE
Snippets.Article_Id =
Article.Articles_Id
)
Snippets
FOR XML PATH('Snippets'),
ELEMENTS XSINIL,
TYPE
)as [*],
--END OF TABLE Snippets (CONDITION SECTION ONLY)
(SELECT COALESCE (Date_of_Capture,'')) AS Date_of_Capture
--(SELECT COALESCE (Article_Record_ID_FK,'')) AS Article_Record_ID_FK,
--(SELECT COALESCE (Article_UniqueID_FK,'')) AS Article_UniqueID_FK,
--(SELECT COALESCE (Title,'')) AS Title,
--(SELECT COALESCE (SnippetText,'')) AS SnippetText,
--(SELECT COALESCE (Adverse_Terms,'')) AS Adverse_Terms,
--(SELECT COALESCE (File_Names,'')) AS File_Names,
--(SELECT COALESCE (Soft_Delete,'')) AS Soft_Delete,
--(SELECT COALESCE (Date_of_Soft_Delete,'')) AS Date_of_Soft_Delete,
--(SELECT COALESCE (Articles_Id,'')) AS Articles_Id
--(SELECT COALESCE (Art_PK_Value_Updated,'')) AS Art_PK_Value_Updated,
--END OF TABLE Article (COLUMNS ONLY)
--START OF TABLE Article (CONDITION SECTION ONLY)
FROM Article
WHERE Article.Art_Id=Articles.Articles_Id
FOR XML PATH('Article'),
ELEMENTS XSINIL,
TYPE
) as [*]
--END OF TABLE Article (CONDITION SECTION ONLY)
--START OF TABLE Articles (CONDITION SECTION ONLY)
FROM Articles
WHERE Articles.Individual_Id = ind.Individual_Id
FOR XML PATH('Articles'),
ELEMENTS XSINIL,
TYPE) AS [*]
--END OF TABLE Articles (CONDITION SECTION ONLY)
--END OF ARTICLES-->ARTICLE-->SNIPPETS-->SNIPPET
FROM Individual ind
WHERE Individual_List_ID_FK = List_ID
FOR XML PATH ('Individual'), ROOT('Individuals'),
elements xsinil,
TYPE)AS [*]
--END OF TABLE Individual
FROM List
--where List_ID = Individual_List_ID_FK
FOR XML PATH('List'),
ROOT ('Lists'),
elements xsinil,
--xmlns:xsi ="",
TYPE) AS FINAL
--END OF TABLE List
And this query output the result to grid like this:
<Lists xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<List>
<List_Type>ARI</List_Type>
<Individuals xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Individual>
<Record_ID>I5021291</Record_ID>
<Record_Type>INDIVIDUAL</Record_Type>
<Title />
<Alternate_Title />
<Forename>David</Forename>
<Middlename>Stanley</Middlename>
<Surname>Lewis</Surname>
<Full_Name>David Stanley Lewis</Full_Name>
<Gender>Male</Gender>
<Source>Media</Source>
<Soft_Delete>N</Soft_Delete>
<Date_of_Soft_Delete>1900-01-01</Date_of_Soft_Delete>
Now how do I move this generated result to the actual XML file in my PC?
October 3, 2022 at 10:23 am
Is this just a one-off, or are you designing a process which needs to run regularly and automatically?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 3, 2022 at 10:41 am
Is this just a one-off, or are you designing a process which needs to run regularly and automatically?
Yes ultimately this will run on daily basis and automatically, but first trying to make the code run manually properly and understanding how the code works...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply