Skip to main content

Reading XML file in SQL

To read the XML file in SQL we need to use sp_xml_preparedocument and :

Syntax

sp_xml_preparedocument
hdoc OUTPUT -- Is the handle to the newly created document. hdoc is an integer.
[ , xmltext ] -- original XML document.
[ , xpath_namespaces ]

Example

DECLARE @hdoc INT,
@params_xml XML = ''

EXEC sp_xml_preparedocument @hdoc OUTPUT, @params_xml
The above command, reads the XML text provided as input, parses the text by using the MSXML parser
sp_xml_preparedocument returns a handle that can be used to access the newly created internal representation of the XML document. This handle is valid for the duration of the session or until the handle is invalidated by executing sp_xml_removedocument.

A parsed document is stored in the internal cache of SQL Server. The MSXML parser uses one-eighth the total memory available for SQL Server. To avoid running out of memory, run sp_xml_removedocument to free up the memory.

PUT XML into variables OPENXML provides a rowset view over an XML document.

Syntax:
OPENXML( idoc int [ in] , rowpattern nvarchar [ in ] , [ flags byte [ in ] ] )

[ WITH ( SchemaDeclaration
TableName ) ]



SELECT @case_id = isnull(CaseID,'1') , @case_id = isnull(CaseID,'1') ,
@noteType_id = isnull(NoteType_Id,'1')
FROM OPENXML(@hdoc, '/Data', 1)
WITH (
CaseID int,
NoteType_Id int
)

select @edocnote_andor = value
FROM OPENXML(@hdoc, '//Data/ANDOR',0)
WITH (value varchar(5))
These Variables can now be used as regular variables in SQL.

To avoid running out of memory, run :
exec sp_xml_removedocument @hdoc
Labels:

Comments

Post a Comment

Popular posts from this blog

Bytes to TeraBytes.

Converting from bytes to terabytes is very easy considering that terabyte = 1024*Gigabyte              = 1024 * 1024 * Megabyte              = 1024 * 1024 * 1024 KiloByte              = 1024 * 1024 * 1024 * 1024 * Byte Query below gives number of documents in a media and the total size of the documents. See how you have used Alias in the query SELECT        [MEDIA] = X.media_id,         [TOTAL] = COUNT(*),       [Size_KB] = SUM (Size_MB) FROM       (       SELECT              d.edoc_id,              d.media_id ,             SIZE_MB =  (                         CAST(ISNULL(d.Size1,0) AS DECIMAL) +      ...
Post a Comment
Read more

JavaScript Interview Questions

This is a compilations of all the interview questions related to Javascript that i have encountered.  Q: Difference between window.onload and onDocumentReady? A: The onload event does not fire until every last piece of the page is loaded, this includes css and images, which means there’s a huge delay before any code is executed. That isnt what we want. We just want to wait until the DOM is loaded and is able to be manipulated. onDocumentReady allows the programmer to do that. Q:  What is the difference between == and === ? A: The == checks for value equality, but === checks for both type and value. Few examples: "1" == 1; // value evaluation only, yields true "1" === 1; // value and type evaluation, yields false "1" == true; // "1" as boolean is true, value evaluation only, yields true "1" === false; // value and type evaluation, yields false Q: What does “1″+2+5 evaluate to? What about 5 + 2 +...
1 comment
Read more

Excel Conditional Logic - Part 2

Excel has a number of logical functions which allows you to set various "conditions" and have data respond to them. For example, you may only want a certain calculation performed or piece of text displayed if certain conditions are met. To start using Functions in Excel you need to Open Excel => Click in a cell => Click on Insert Menu button =>  Function To read about If-Else condition click on Excel Conditional Logic - Part 1 . Here we will be talking about AND, OR and NOT Condition AND CONDITION This function is a logical test to see if all conditions are true. If this is the case, the value "TRUE" is returned. If any of the arguments in the AND statement are found to be false, the whole statement produces the value "FALSE". This function is particularly useful as a check to make sure that all conditions you set are met. Arguments are entered in the AND statement in parentheses, separated by commas, and there is a maximum of 30 a...
Post a Comment
Read more