Saturday, 27 December 2014

SQL Server: How to properly insert several values from XML parameter



I am pretty new to XML and hope someone here can help me with this.


I use JavaScript and Ajax to pass 4 values from a form as an XML string and SQL Server 2012 for the stored procedure.


My JS (relevant parts):



var title = $('#title').val();
var summary = $('#summary').val();
var post = $('#details').val();
var departmentID = $('#departmentID').val();

var xmlMain = '<root><title>' + title + '</title><summary>' + summary + '</summary><post>' + post + '</post><departmentID>' + departmentID + '</departmentID></root>';


My SQL (relevant parts - parameter @xmlMain is defined as xml):



INSERT INTO RC_Posts
(
title,
summary,
post,
departmentID
)
OUTPUT inserted.postID INTO @temp(insertRef)
SELECT (
SELECT ParamValues.title.value('.','nvarchar(100)')
FROM @xmlMain.nodes('/root/title') as ParamValues(title)
),
(
SELECT ParamValues.summary.value('.','nvarchar(500)')
FROM @xmlMain.nodes('/root/summary') as ParamValues(summary)
),
(
SELECT ParamValues.post.value('.','nvarchar(max)')
FROM @xmlMain.nodes('/root/post') as ParamValues(post)
),
(
SELECT ParamValues.departmentID.value('.','int')
FROM @xmlMain.nodes('/root/departmentID') as ParamValues(departmentID)
)


My main questions here are:



  1. Do I have to write the Select part like this or is there a better / easier way to structure this ?

  2. What do I have to enter for the values, i.e. where I currently have '.' ?


Note: My general JS, Ajax and SQL was working before when just passing standard nvarchar / int values but I now need to pass these as XML in order to keep certain special characters etc.


Many thanks for any help with this, Tim.


No comments:

Post a Comment