CODEWITHSUNDEEP
sqlsql-serverxmlstored-procedures
user2571510
user2571510

Reputation: 11377

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

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:

  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.

Upvotes: 1

Views: 345

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93704

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

Upvotes: 3

Related Questions