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

Using the XML Data Type - SQL Server

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)

Generating XML file with XSL Reference¶

Overview¶

When you use the FOR XML clause in T-SQL, you don't end up with what could be saved into a valid XML file — there's no root element. Furthermore, if you wanted your XML file to reference an XSL stylesheet, that would also be missing. The following sample demonstrates how to address both of these issues.

T-SQL Code¶

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)

Sample MyXslFile.xsl File¶

<?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>