Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

Page History: Using the XML Data Type - SQL Server

Compare Page Revisions



« 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)

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.