Page History: Using the XML Data Type - SQL Server

Compare Page Revisions

4 3 2 1 0 Current 4 3 2 1 0

« Older Revision - Back to Page History - Newer Revision »

Page Revision: Tue, Apr 06, 2010, 9:46 AM

In a WHERE clause¶

Given: (1) @ResourceId is an XML input parameter in the form <x v="1"/><x v="13"/>... (2) We want to find all records where PyramidId is one of the values in @ResourceId

Syntax:

...

where 1=1
    and @ResourcePyramids.exist('/x[@v = sql:column("PyramidId")]') = 1

To Submit a Table of Data to a Stored Procedure¶

/*--------------------------------------------------------------------------------
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
        )

Extracting Data From an XML Field as Multiple Rows¶

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