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: Wed, Mar 06, 2013, 4:57 PM

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

Using XML Namespaces¶

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)