Compare Page Revisions
« Older Revision - Back to Page History - Current Revision
@ResourceId
<x v="1"/><x v="13"/>...
PyramidId
... where 1=1 and @ResourcePyramids.exist('/x[@v = sql:column("PyramidId")]') = 1
/*-------------------------------------------------------------------------------- Node Names for the @dataSet input parameter. Root Node = <NewDataSet> RplProjectResourceRole: GridRow, RplProjectResourceRoleId, RplProjectId, RoleId, ResourceId PlannedAllocation: GridRow, RplProjectResourceRoleId, AmcYear, AmcMonth, AllocationPct --------------------------------------------------------------------------------*/ ;with InputDataSet as ( select RplProjectResourceRoleId = T.c.value('RplProjectResourceRoleId[1]','int'), AmcYear = T.c.value('AmcYear[1]','int'), AmcMonth = T.c.value('AmcMonth[1]','int'), AllocationPct = T.c.value('AllocationPct[1]','decimal(8,2)') from @dataSet.nodes('/NewDataSet/PlannedAllocation') T(c) ) update PlannedAllocation set AllocationPct = ( select AllocationPct from InputDataSet where 1=1 and RplProjectResourceRoleId = PlannedAllocation.RplProjectResourceRoleId and AmcYear = PlannedAllocation.AmcYear and AmcMonth = PlannedAllocation.AmcMonth and @planTypeId = PlannedAllocation.PlanTypeId ), LastUpdatedBy = @userid, LastUpdatedOn = getdate() WHERE 1=1 AND EXISTS ( select 2 from InputDataSet where 1=1 and RplProjectResourceRoleId = PlannedAllocation.RplProjectResourceRoleId and AmcYear = PlannedAllocation.AmcYear and AmcMonth = PlannedAllocation.AmcMonth and @planTypeId = PlannedAllocation.PlanTypeId )
with MyLyrics as ( select SongId ,TitleExtended ,CreditsExtended ,Lyric = T.c.value('.', 'varchar(MAX)') from SongExtended cross apply Lyrics.nodes('/song/section/line') T(c) where 1=1 and Lyrics is not null ) select SongId ,TitleExtended ,CreditsExtended ,Lyric from MyLyrics
with xmlnamespaces( default 'http://www.w3.org/2005/Atom' ,'http://schemas.microsoft.com/ado/2007/08/dataservices' as d ,'http://schemas.microsoft.com/ado/2007/08/dataservices/metadata' as m ) select CustomerId = T.c.value('d:CustomerId [1]', 'int') ,OrderId = T.c.value('d:OrderId[1]', 'varchar(100)') ,Descrip = T.c.value('d:Description[1]', 'varchar(100)') from @locationsXml.nodes('/feed/entry/content/m:properties') as T(c)
declare @xsl nvarchar(1000) = '<?xml-stylesheet type="text/xsl" href="MyXslFile.xsl"?>' declare @result nvarchar(max) = ( select modules = convert(xml, (select * from dbo.Module for xml path ('module'), elements xsinil)) ,tenants = convert(xml, (select * from dbo.Tenant for xml path ('tenant'), elements xsinil)) for xml path ('data') ) select result = convert(xml, @xsl + @result)
<?xml version="1.0" encoding="ISO-8859-1"?> <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxml="urn:schemas-microsoft-com:xslt" version="1.0"> <xsl:template match="/*"> <html> <head> </head> <body> <xsl:for-each select="*"> <h1><xsl:value-of select="name()"/></h1> <table cellpadding="3" border="1"> <thead> <xsl:for-each select="*[1]/*"> <th> <xsl:value-of select="name()" /> </th> </xsl:for-each> </thead> <tbody> <xsl:for-each select="*"> <tr> <xsl:for-each select="*"> <td> <xsl:value-of select="." /> </td> </xsl:for-each> </tr> </xsl:for-each> </tbody> </table> </xsl:for-each> </body> </html> </xsl:template> </xsl:stylesheet>
ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.