4-xbyk-content-hub-asset-query.sql

SELECT JSON_VALUE(ImageFile, '$.Identifier') AS Identifier
    ,JSON_VALUE(ImageFile, '$.Name') AS Name
    ,JSON_VALUE(ImageFile, '$.Extension') AS Extension
    ,JSON_VALUE(ImageFile, '$.Size') AS Size
    ,JSON_VALUE(ImageFile, '$.LastModified') AS LastModified
    ,LOWER(CONCAT(
        '/assets/ContentItems/', 
        LEFT(ci.ContentItemGUID, 2), 
        '/',
        ci.ContentItemGUID, 
        '/',
        CAST(ClassFormDefinition AS XML).value('(/form/field[@columntype="contentitemasset"]/@guid)[1]', 'NVARCHAR(50)'),
        '/',
        JSON_VALUE(ImageFile, '$.Identifier'), 
        JSON_VALUE(ImageFile, '$.Extension')
    )) AS FilePathOnDisk
FROM CMS_ContentItem ci
LEFT JOIN CMS_ContentItemCommonData cicd
    ON cicd.ContentItemCommonDataContentItemID = ci.ContentItemID
LEFT JOIN CMS_ContentItemLanguageMetadata cilm
    ON cicd.ContentItemCommonDataContentLanguageID = cilm.ContentItemLanguageMetadataContentLanguageID
    AND cicd.ContentItemCommonDataContentItemID = cilm.ContentItemLanguageMetadataContentItemID
LEFT JOIN CMS_ContentLanguage cl
    ON cl.ContentLanguageID = cilm.ContentItemLanguageMetadataContentLanguageID
LEFT JOIN CMS_Class c
    ON ci.ContentItemContentTypeID = c.ClassID
LEFT JOIN DancingGoat_Image im
    ON c.ClassName = 'DancingGoat.Image'
    AND cicd.ContentItemCommonDataID = im.ContentItemDataCommonDataID
WHERE ci.ContentItemGUID = '279bb55f-81e3-4d93-9cff-8372dc34cdc5'