2007/12/10

Convert xml to table in SQL 2005

This is how I convert XML string into a Table in SQL 2005:
DECLARE @tempTable TABLE (
userId INT,
userName NVARCHAR(50),
password NVARCHAR(50)
)
DECLARE @xml XML
SET @xml='
<row userId="67" userName="Kenny1" password="1234" />
<row userId="80" userName="Kenny2" password="5678" />'

INSERT INTO @tempTable
SELECT Tbl.Col.value('@userId', 'INT'),
       Tbl.Col.value('@userName', 'NVARCHAR(50)'),
       Tbl.Col.value('@password', 'NVARCHAR(50)')
FROM   @xml.nodes('//row') Tbl(Col)

--See the table
SELECT * FROM @tempTable 

2007/12/09

Web page search and SQL

Here is a way to search many columns in a table:
CREATE PROCEDURE [dbo].[SearchExample] (
@userId UNIQUEIDENTIFIER = NULL,   --set all parameters' default value to null
@firstName NVARCHAR(50) = NULL,
@lastName NVARCHAR(50) = NULL,
@address NVARCHAR(100) = NULL,
@zipcode INT = NULL
)

SELECT ...  --omit
FROM   dbo.UserInfo u
WHERE  u.userId = COALESCE(@userId, u.userId)
AND    u.firstName LIKE ('%'+ COALESCE(@firstName, u.firstName)+'%')
AND    ...  --omit
...    --omit
Set the default value to null for all the input parameters is because if we don't have any filter to search (the value is null), then COALESCE() will use the second parameter as the output, otherwise it will output the first parameter.
For example, if @userId = 1001, the
WHERE  u.userId = COALESCE(@userId, u.userId)
will become like
WHERE  u.userId = 1001
Otherwise, it will become like
WHERE  u.userId = u.userId