Post reply

Can this be executed as dynamic sql?

  • June 30, 2010 at 2:47 pm

    #224351

    Where @xmldata below is of type XML, can I write this as a dynamic query?

    I have built up the result set programmatically and need to execute it dynamically if possible.

    INSERT STAGE_UCF_Assets_List

    SELECT

    t.c.value('UCF_Basic_Info[1]/UCF_Asset_Category[1]', 'VARCHAR(MAX)') UCF_Asset_Category,

    t.c.value('UCF_Basic_Info[1]/UCF_Asset_Class[1]', 'VARCHAR(MAX)') UCF_Asset_Class,

    t.c.value('UCF_Basic_Info[1]/UCF_Asset_Common_Name[1]', 'VARCHAR(MAX)') UCF_Asset_Common_Name,

    t.c.value('UCF_Basic_Info[1]/UCF_Asset_Edition[1]', 'VARCHAR(MAX)') UCF_Asset_Edition,

    t.c.value('UCF_Basic_Info[1]/UCF_Asset_Name[1]', 'VARCHAR(MAX)') UCF_Asset_Name,

    t.c.value('UCF_Basic_Info[1]/UCF_Asset_Path[1]', 'VARCHAR(MAX)') UCF_Asset_Path,

    t.c.value('UCF_Basic_Info[1]/UCF_Asset_Platform[1]', 'VARCHAR(MAX)') UCF_Asset_Platform,

    t.c.value('UCF_Basic_Info[1]/UCF_Asset_Type[1]', 'VARCHAR(MAX)') UCF_Asset_Type,

    t.c.value('UCF_Basic_Info[1]/UCF_Asset_Update[1]', 'VARCHAR(MAX)') UCF_Asset_Update,

    t.c.value('UCF_Basic_Info[1]/UCF_Asset_Version[1]', 'VARCHAR(MAX)') UCF_Asset_Version,

    t.c.value('UCF_Basic_Info[1]/UCF_CPE_Name[1]', 'VARCHAR(MAX)') UCF_CPE_Name,

    t.c.value('UCF_Basic_Info[1]/UCF_NVD_ID[1]', 'VARCHAR(MAX)') UCF_NVD_ID,

    t.c.value('UCF_Meta_Data[1]/UCF_Asset_Date_Added[1]', 'VARCHAR(MAX)') UCF_Asset_Date_Added,

    t.c.value('UCF_Meta_Data[1]/UCF_Asset_Date_Modified[1]', 'VARCHAR(MAX)') UCF_Asset_Date_Modified,

    t.c.value('UCF_Meta_Data[1]/UCF_Asset_Deprecated_By[1]', 'VARCHAR(MAX)') UCF_Asset_Deprecated_By,

    t.c.value('UCF_Meta_Data[1]/UCF_Asset_Deprecation_Notes[1]', 'VARCHAR(MAX)') UCF_Asset_Deprecation_Notes,

    t.c.value('UCF_Meta_Data[1]/UCF_Asset_Genealogy[1]', 'VARCHAR(MAX)') UCF_Asset_Genealogy,

    t.c.value('UCF_Meta_Data[1]/UCF_Asset_ID[1]', 'VARCHAR(MAX)') UCF_Asset_ID,

    t.c.value('UCF_Meta_Data[1]/UCF_Asset_ID_CheckDigit[1]', 'VARCHAR(MAX)') UCF_Asset_ID_CheckDigit,

    t.c.value('UCF_Meta_Data[1]/UCF_Asset_Live_Status[1]', 'VARCHAR(MAX)') UCF_Asset_Live_Status,

    t.c.value('UCF_Meta_Data[1]/UCF_Asset_Release_Version[1]', 'VARCHAR(MAX)') UCF_Asset_Release_Version,

    t.c.value('UCF_Meta_Data[1]/UCF_Asset_SNED[1]', 'VARCHAR(MAX)') UCF_Asset_SNED,

    t.c.value('UCF_Meta_Data[1]/UCF_Asset_Sort_ID[1]', 'VARCHAR(MAX)') UCF_Asset_Sort_ID,

    t.c.value('UCF_References[1]/UCF_Vendor_ID[1]', 'VARCHAR(MAX)') UCF_Vendor_ID

    FROM @xmldata.nodes('//UCF_Asset_Item') t(c)

  • June 30, 2010 at 3:26 pm

    #1187358

    I have figured this out.

    The reason I could not reference the @xmldata variable was becuase I needed to declare and assign it within the dynamic sql.

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

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