Handling XML in SQL Server

Question

I am not very familiar with using XML in SQL Server and I have been asked to update this stored procedure and modify it to apply some math to a field before inserting it into the table.

Here is some example code:

CREATE PROCEDURE [spNewUser]
    @UserInfo xml
AS    
BEGIN
    IF @UserInfo.exist('/UserInfo') = 1
    BEGIN
        DECLARE 
            @FirstName varchar(40),
            @LastName varchar(40),
            @Height varchar(10)
        SELECT
            @FirstName = Personal.query('FirstName').value('.','varchar(25)'),
            @LastName = Personal.query('LastName').value('.','varchar(50)'),
            @Height = NULLIF(Personal.query('HeightFt').value('.','varchar(3)') + '.' + Personal.query('HeightIn').value('.','varchar(3)'),'.')
        FROM
            @UserInfo.nodes('/UserInfo/Personal') AppInfo(Personal)
    END
BEGIN

Now I understand (in general) everything happening. We used to store height (in feet and inches) as 'FootValue'.'InchValue' because we didn't use it for anything, but now we want to use it for something. We are going to store it as an int now, and save the number of inches. I want to do HeightFt * 12 + HeightIn but I am unsure the safest way to convert and do the math. I am not sure how to do the query('HeightFt').value(???) statement. I would want it to return 0 if not existent.

marc_s · Accepted Answer · 2011-07-31 19:40:32Z

2

I would recommend to store numeric values as numerics - not varchar(10). Basically, you should extract those values from the XML and then do your calculation afterwards:

DECLARE 
   @FirstName varchar(40),
   @LastName varchar(40),
   @HeightFt decimal(16, 4),
   @HeightIn decimal(16, 4)

SELECT
    @FirstName = Personal.value('(FirstName)[1]', 'varchar(25)'),
    @LastName = Personal.value('(LastName)[1]', 'varchar(50)'),
    @HeightFt = ISNULL(Personal.value('(HeightFt)[1]', 'DECIMAL(6, 1)'), 0.0),
    @HeightIn = ISNULL(Personal.value('(HeightIn)[1]', 'DECIMAL(6, 1)'), 0.0)
FROM
  @UserInfo.nodes('/UserInfo/Personal') AppInfo(Personal)

DECLARE @Calculated DECIMAL(8, 2)

SET @Calculated = @HeightFt * 12 + @HeightIn

edited Jul 31, 2011 at 19:40

answered Jul 31, 2011 at 19:32

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Andrew Jackman Over a year ago

What will this return if HeightFt or HeightIn doesn't exist?

marc_s Over a year ago

@Remus: ok, sure - not fitting here; that's just my "usual" default (for invoices and stuff like that) ... adapt as needed and as you see fit :-)

Andrew Jackman Over a year ago

@marc Why did you reply to his and not mine :p I just want to know what the default value is!

Andrew Jackman Over a year ago

@marc sorry if I didn't show it very well, I meant a bit of humour (and wasn't upset!) in that last comment. Thanks for your help, this worked out great!

Collectives™ on Stack Overflow

Handling XML in SQL Server

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related