How to properly insert several values from XML parameter in SQL Server?

Question

I am pretty new to XML.

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 are:

Do I have to write the Select part like this or is there a better / easier way to structure this?
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.

Pரதீப் · Accepted Answer · 2014-12-27 16:00:26Z

3

You can Use single Xml Nodes() function to extract values from XML

Change your insert like this

INSERT INTO RC_Posts
            (title,summary,post,departmentID)
OUTPUT      inserted.postID
INTO @temp(insertRef)
SELECT [Xml_Tab].[Cols].value('(title)[1]', 'varchar(50)'),
       [Xml_Tab].[Cols].value('(summary)[1]', 'varchar(50)'),
       [Xml_Tab].[Cols].value('(post)[1]', 'varchar(50)'),
       [Xml_Tab].[Cols].value('(departmentID)[1]', 'varchar(50)')
FROM   @xml.nodes('/root')AS [Xml_Tab]([Cols])

Example:

DECLARE @xml XML ='<root>
<title> XML Demo </title>
<summary> Working of Xml Nodes </summary>
<post> Developer </post>
<departmentID> CS </departmentID>
</root>'

SELECT title=[Xml_Tab].[Cols].value('(title)[1]', 'varchar(50)'),
       summary=[Xml_Tab].[Cols].value('(summary)[1]', 'varchar(50)'),
       post=[Xml_Tab].[Cols].value('(post)[1]', 'varchar(50)'),
       departmentID=[Xml_Tab].[Cols].value('(departmentID)[1]', 'varchar(50)')
FROM   @xml.nodes('/root')AS [Xml_Tab]([Cols])

Output :

title        summary                 post        departmentID
--------     --------------------    ---------   ------------
XML Demo     Working of Xml Nodes    Developer   CS

edited Dec 27, 2014 at 16:00

answered Dec 27, 2014 at 9:53

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

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

2 Comments

user2571510 Over a year ago

This is perfect - thanks a lot for ! Can you tell me what the [1] means here ?

Pரதீப் Over a year ago

@user2571510 If you are going to have two title tags inside root tag the [1] will fetch the first title tag value [2] will fetch second value ex ` <title> XML Demo </title> <title> XML Demo1 </title>` here [1]=XML Demo and [2]=XML Demo1

Collectives™ on Stack Overflow

How to properly insert several values from XML parameter in SQL Server?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related