xml parameters in stored procedure 2012

kowsika devi
kowsika devi used Ask the Experts™
on
Hi,
  how to write stored procedure to pass xml as input parameter in sql server 2012 explain with simple example .
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Expert
Awarded 2016
Top Expert 2016
Commented:
Please find the entire tested code for you-

In this case we are passing the complex XML to the procedure and getting the element from it by using Cross apply method.

--Stored Procedure

CREATE PROC ProcessXML ( @a XML )
AS

BEGIN

SELECT @a  k INTO InputXML1
 
	SELECT * FROM
	(
		SELECT
			 a.b.value('areaName[1]', 'VARCHAR(100)') AS 'areaName'
			,a.b.value('(portId)[1]', 'VARCHAR(100)') AS 'portId'
			,a.b.value('(portName)[1]', 'VARCHAR(100)') AS 'portName'   
		FROM InputXML1 as s
			OUTER APPLY s.k.nodes('loadingAreas/LoadingArea') as a(b)		
	)u 
 

END

Open in new window


--Calling code

EXEC ProcessXML '
  <loadingAreas>
    <LoadingArea>
      <areaName>Antartic</areaName>
      <portId>0021</portId>
      <portName>Houston</portName>
    </LoadingArea>
      <LoadingArea>
      <areaName>WCMEX</areaName>
      <portId>0004</portId>
      <portName>Manzanillo, Mexico</portName>
    </LoadingArea>
  </loadingAreas> 
'

Open in new window


OUTPUT

/*------------------------
OUTPUT
------------------------*/

(1 row(s) affected)
areaName              portId  portName
--------------------- ------- -----------------------
Antartic              0021    Houston
WCMEX                 0004    Manzanillo, Mexico

(2 row(s) affected)

Open in new window

Author

Commented:
SET @p_doc = @ExcelXml

          EXEC Sp_xml_preparedocument
            @p_idoc OUTPUT,
            @p_doc

what's the  purpuse need to go with preparexmldocument sp jj
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016
Commented:
This read and parses XML using  MSXML parser. Basically after this we can say out xml is proper and formatted.

You can read all the details about this from Microsoft - https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-xml-preparedocument-transact-sql

You can use it like below -

DECLARE @hdoc int;  
DECLARE @doc varchar(1000);  
SET @doc ='  
<ROOT>  
<loadingAreas>
    <LoadingArea>
      <areaName>Antartic</areaName>
      <portId>0021</portId>
      <portName>Houston</portName>
    </LoadingArea>
      <LoadingArea>
      <areaName>WCMEX</areaName>
      <portId>0004</portId>
      <portName>Manzanillo, Mexico</portName>
    </LoadingArea>
</loadingAreas>   
</ROOT>';  
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc, '<ROOT xmlns:xyz="urn:MyNamespace"/>'; 

Open in new window


if you have error in xml it will give u error like below -

/*------------------------
DECLARE @hdoc int;  
DECLARE @doc varchar(1000);  
SET @doc ='  
<ROOT>  
<loadingAreas>
    <LoadingArea>
      <areaName>Antartic</areaName>
      <portId>0021</portId>
      <portName>Houston</portName>
    </LoadingArea>
      <LoadingArea>
      <areaName>WCMEX</areaName>
      <portId>0004</portId>
      <portName>Manzanillo, Mexico</portName>
    </LoadingArea>
 
</ROOT>';  
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc, '<ROOT xmlns:xyz="urn:MyNamespace"/>'; 
------------------------*/
The XML parse error 0xc00ce56d occurred on line number 15, near the XML text "</ROOT>".
Msg 6602, Level 16, State 2, Procedure sp_xml_preparedocument, Line 1
The error description is 'End tag 'ROOT' does not match the start tag 'loadingAreas'.'.

Open in new window

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Provided tested solution.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial