CREATE PROCEDURE dbo.uspCreatePassword ( @UpperCaseItems SMALLINT, @LowerCaseItems SMALLINT, @NumberItems SMALLINT, @SpecialItems SMALLINT ) AS SET NOCOUNT ON -- Initialize some variables DECLARE @UpperCase VARCHAR(26), @LowerCase VARCHAR(26), @Numbers VARCHAR(10), @Special...
This blog post is continued here http://weblogs.sqlteam.com/peterl/archive/2009/07/03/Manipulate-XML-data-with-non-xml-columns-and-not-using-variable.aspx
/*******************************************************************************
Prepare script with testcase
*******************************************************************************/
DECLARE @XMLString XML,
@ElementEqual VARCHAR(50),
@ElementLike VARCHAR(50),
@AttributeEqual VARCHAR(50),
@AttributeLike VARCHAR(50)
SELECT @XMLString = '
<Customers>
<Customer>
<FirstName>Kevin</FirstName>
<LastName>Goff</LastName>
<City type="aca">Camp Hill</City>
</Customer>
<Customer>
<FirstName>Steve</FirstName>
<LastName>Goff</LastName>
<City type="acb">
Philadelphia</City>
</Customer>
</Customers>',
@ElementEqual = 'Camp Hill',
@ElementLike = 'adel',
@AttributeEqual = 'acb',
@AttributeLike = 'c'
/*******************************************************************************
Search for City-nodes
*******************************************************************************/
-- Get all City where element is equal to "Camp Hill"
SELECT cust.coldef.value('FirstName[1]','VARCHAR(20)') AS FirstName,
...