xml parameters in stored procedure 2012

Hi,
  how to write stored procedure to pass xml as input parameter in sql server 2012 explain with simple example .
kowsika deviAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kowsika deviAuthor 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
0
Pawan KumarDatabase ExpertCommented:
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

0
Pawan KumarDatabase ExpertCommented:
Provided tested solution.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.