Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DECLARE @NodeId int, @NodeXml xml
- SET @NodeId = 1261
- SET @NodeXml =
- (SELECT TOP 1 cx.[xml]
- FROM cmsContentXml cx JOIN
- cmsContent cc ON cc.nodeId = cx.nodeId
- WHERE cc.nodeId = @NodeId) DECLARE @Name nvarchar(1000)
- select @NodeXml
- SET @Name =
- (SELECT un.text Name
- FROM cmsContentXml xt JOIN
- umbracoNode un ON un.id = xt.NodeId
- WHERE xt.nodeId = @NodeId) DECLARE @Date Datetime
- SET @Date =
- (SELECT @NodeXml .value('(/TechnicalData/@createDate)[1]', 'datetime')
- FROM cmsContentXml xt JOIN
- umbracoNode un ON un.id = xt.NodeId
- WHERE xt.nodeId = @NodeId) DECLARE @Title nvarchar(1000)
- SET @Title =
- (SELECT @NodeXml .query('//TechnicalData/title' ).value('.', 'nvarchar(max)') Title
- FROM cmsContentXml xt JOIN
- umbracoNode un ON un.id = xt.NodeId
- WHERE xt.nodeId = @NodeId) DECLARE @Subtitle nvarchar(1000)
- SET @Subtitle =
- (SELECT @NodeXml .query('//TechnicalData/subtitle' ).value('.', 'nvarchar(max)') AS Subtitle
- FROM cmsContentXml xt JOIN
- umbracoNode un ON un.id = xt.NodeId
- WHERE xt.nodeId = @NodeId) DECLARE @Company int
- SET @Company =
- (SELECT @NodeXml .query('//TechnicalData/bedrijf' ).value('.', 'int') Company
- FROM cmsContentXml xt JOIN
- umbracoNode un ON un.id = xt.NodeId
- WHERE xt.nodeId = @NodeId) DECLARE @Plaatsingsvoorschriften nvarchar(max)
- SET @Plaatsingsvoorschriften =
- (SELECT @NodeXml .query('//TechnicalData/plaatsingsvoorschriften' ).value('.', 'nvarchar(max)') Plaatsingsvoorschriften
- FROM cmsContentXml xt JOIN
- umbracoNode un ON un.id = xt.NodeId
- WHERE xt.nodeId = @NodeId) DECLARE @Toepassing nvarchar(max)
- SET @Toepassing =
- (SELECT @NodeXml .query('//TechnicalData/toepassing' ).value('.', 'nvarchar(max)') Toepassing
- FROM cmsContentXml xt JOIN
- umbracoNode un ON un.id = xt.NodeId
- WHERE xt.nodeId = @NodeId) DECLARE @TechnischeTekening nvarchar(max)
- SET @TechnischeTekening =
- (SELECT @NodeXml .query('//TechnicalData/technischeTekening' ).value('.', 'nvarchar(max)') TechnischeTekening
- FROM cmsContentXml xt JOIN
- umbracoNode un ON un.id = xt.NodeId
- WHERE xt.nodeId = @NodeId) DECLARE @Eigenschappen nvarchar(max)
- SET @Eigenschappen =
- (SELECT @NodeXml .query('//TechnicalData/eigenschappen' ).value('.', 'nvarchar(max)') Eigenschappen
- FROM cmsContentXml xt JOIN
- umbracoNode un ON un.id = xt.NodeId
- WHERE xt.nodeId = @NodeId) DECLARE @Certificatie nvarchar(max)
- SET @Certificatie =
- (SELECT @NodeXml .query('//TechnicalData/certificatie' ).value('.', 'nvarchar(max)') Certificatie
- FROM cmsContentXml xt JOIN
- umbracoNode un ON un.id = xt.NodeId
- WHERE xt.nodeId = @NodeId) DECLARE @Foto1 int
- SET @Foto1 =
- (SELECT @NodeXml .query('//TechnicalData/foto1' ).value('.', 'int') foto1
- FROM cmsContentXml xt JOIN
- umbracoNode un ON un.id = xt.NodeId
- WHERE xt.nodeId = @NodeId) DECLARE @Foto2 int
- SET @Foto2 =
- (SELECT @NodeXml .query('//TechnicalData/foto2' ).value('.', 'int') foto2
- FROM cmsContentXml xt JOIN
- umbracoNode un ON un.id = xt.NodeId
- WHERE xt.nodeId = @NodeId) DECLARE @Link nvarchar(max)
- SET @Link =
- (SELECT @NodeXml .query('//TechnicalData/link' ).value('.', 'nvarchar(max)') link
- FROM cmsContentXml xt JOIN
- umbracoNode un ON un.id = xt.NodeId
- WHERE xt.nodeId = @NodeId)
- /* COMPANY DATA*/ SET @NodeId = @Company
- SET @NodeXml =
- (SELECT TOP 1 cx.[xml]
- FROM cmsContentXml cx JOIN
- cmsContent cc ON cc.nodeId = cx.nodeId
- WHERE cc.nodeId = @NodeId) DECLARE @CompanyName nvarchar(500)
- SET @CompanyName =
- (SELECT @NodeXml .value('(/Company/@nodeName)[1]', 'nvarchar(500)') CompanyName
- FROM cmsContentXml xt JOIN
- umbracoNode un ON un.id = xt.NodeId
- WHERE xt.nodeId = @NodeId) DECLARE @Email nvarchar(500)
- SET @Email =
- (SELECT @NodeXml .value('(/Company/email)[1]', 'nvarchar(500)') Email
- FROM cmsContentXml xt JOIN
- umbracoNode un ON un.id = xt.NodeId
- WHERE xt.nodeId = @NodeId) DECLARE @Address nvarchar(500)
- SET @Address =
- (SELECT @NodeXml .value('(/Company/address)[1]', 'nvarchar(500)') [Address]
- FROM cmsContentXml xt JOIN
- umbracoNode un ON un.id = xt.NodeId
- WHERE xt.nodeId = @NodeId) DECLARE @Tel nvarchar(500)
- SET @Tel =
- (SELECT @NodeXml .value('(/Company/tel)[1]', 'nvarchar(500)') Tel
- FROM cmsContentXml xt JOIN
- umbracoNode un ON un.id = xt.NodeId
- WHERE xt.nodeId = @NodeId) DECLARE @NtmbErkend bit
- SET @NtmbErkend =
- (SELECT @NodeXml .value('(/Company/ntmbErkend)[1]', 'bit') NtmbErkend
- FROM cmsContentXml xt JOIN
- umbracoNode un ON un.id = xt.NodeId
- WHERE xt.nodeId = @NodeId) DECLARE @Website nvarchar(1000)
- SET @Website =
- (SELECT @NodeXml .value('(/Company/website)[1]', 'nvarchar(1000)') Website
- FROM cmsContentXml xt JOIN
- umbracoNode un ON un.id = xt.NodeId
- WHERE xt.nodeId = @NodeId)
- SET @NodeId = @Foto1
- SET @NodeXml =
- (SELECT TOP 1 cx.[xml]
- FROM cmsContentXml cx JOIN
- cmsContent cc ON cc.nodeId = cx.nodeId
- WHERE cc.nodeId = @NodeId) DECLARE @Foto1Url nvarchar(1000)
- SET @Foto1Url =
- (SELECT @NodeXml .value('(/Image/umbracoFile)[1]', 'nvarchar(1000)')
- FROM cmsContentXml xt JOIN
- umbracoNode un ON un.id = xt.NodeId
- WHERE xt.nodeId = @NodeId)
- SET @NodeId = @Foto2
- SET @NodeXml =
- (SELECT TOP 1 cx.[xml]
- FROM cmsContentXml cx JOIN
- cmsContent cc ON cc.nodeId = cx.nodeId
- WHERE cc.nodeId = @NodeId) DECLARE @Foto2Url nvarchar(1000)
- SET @Foto2Url =
- (SELECT @NodeXml .value('(/Image/umbracoFile)[1]', 'nvarchar(1000)')
- FROM cmsContentXml xt JOIN
- umbracoNode un ON un.id = xt.NodeId
- WHERE xt.nodeId = @NodeId)
- SELECT @Name Name, @Date [Date],
- iif(CHARINDEX('"values":null', @Title,0) > 0,'',Substring(@Title, 21, CHARINDEX('}', @Title) - 22)) Title,
- iif(CHARINDEX('"values":null', @Subtitle,0) > 0,'',Substring(@Subtitle, 21, CHARINDEX('}', @Subtitle) - 22)) Subtitle,
- iif(CHARINDEX('"values":null', @Plaatsingsvoorschriften,0) > 0,'',Substring(@Plaatsingsvoorschriften, 21, CHARINDEX('}', @Plaatsingsvoorschriften) - 22)) Plaatsingsvoorschriften,
- iif(CHARINDEX('"values":null', @Toepassing,0) > 0,'',Substring(@Toepassing, 21, CHARINDEX('}', @Toepassing) - 22)) Toepassing,
- iif(CHARINDEX('"values":null', @TechnischeTekening,0) > 0,'',Substring(@TechnischeTekening, 21, CHARINDEX('}', @TechnischeTekening) - 22)) TechnischeTekening,
- iif(CHARINDEX('"values":null', @Eigenschappen,0) > 0,'',Substring(@Eigenschappen, 21, CHARINDEX('}', @Eigenschappen) - 22)) Eigenschappen,
- iif(CHARINDEX('"values":null', @Certificatie,0) > 0,'',Substring(@Certificatie, 21, CHARINDEX('}', @Certificatie) - 22)) Certificatie,
- @Link Link, @Foto1Url Foto1, @Foto2Url Foto2, @CompanyName CompanyName, @Tel Tel, @Address [Address],
- @Website Website, @NtmbErkend NtmbErkend, @Email Email
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement