How to copy XML converted data to file on local drive?

  • 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?

     

  • 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.

  • Phil Parkin wrote:

    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...

    • This reply was modified 2 months ago by  Jobs90312.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply