http://www.sqlservercentral.com/columnists/dPriyankara/userdefineddatatypesinsqlserver2005.asp

User defined data types  in SQL SERVER 2005

 

 

http://www.sqlservercentral.com/columnists/dPriyankara/userdefineddatatypesinsqlserver2005.asp

--
Regards,
Munish Kalra

http://kalraonaspnet.blogspot.com
http://munishmca.co.nr

How to upload XML doc into sql server 2005 using openxml

This XML file is used to store in database in table with fields company and ph

 

<ROOT>
<ShipperRec Company="ABC Shippers" Ph="(503) 555-9191" />
</ROOT>

 

Code given below is used to determine the levels use control struct to give your each document valid inputs handlers. 

 

 

SELECT *
FROM OPENXML (@idoc, '/ROOT/ShipperRec',1)
WITH (Company varchar(80),
Ph varchar(48)
)

 

 

Code is pure in sql queries . just make a stored procedure pass your document to parse and done. if you find any problem .  i am here to serve you .

 

 

This is a very simple example illustrating how we can use OPENXML as a rowset provider in conjunction with SELECT T-SQL statement:

DECLARE @idoc int // Document handler

DECLARE @doc varchar (1000)

SET @doc ='
<ROOT>
<ShipperRec Company="ABC Shippers" Ph="(503) 555-9191" />
</ROOT>'


// XML DOC USED
--Create an internal representation of the XML document
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

-- Execute a SELECT statement that uses the OPENXML rowset provider
SELECT *
FROM OPENXML (@idoc, '/ROOT/ShipperRec',1)
WITH (Company varchar(80),
Ph varchar(48)
)

-- Clear the XML document from memory
EXEC sp_xml_removedocument @idoc


The above lines of code simply declares two variables, one to hold document handle and other variable is a XML string. In this example we are directly assigning the XML text to the variable, but remember that this XML document can come from any source, for instance an web application form post, or two-tier Microsoft Visual Basic client application, etc.

The three important statements in above code include calling sp_xml_preparedocument to load the XML document string @doc. Next, we call SELECT statement with using OPENXML in the FROM clause. The OPENXML uses an attribute-centric mapping and its schema definition specifies output rowset to have two columns: Company and Ph. The XPath row pattern (/ROOT/ShipperRec) identifies the node in XML tree to work on. Finally, we call to remove XML document from memory. The output of above example execution looks like:

 

Company            Ph ---------------    ------------------ ABC Shippers       (503) 555-9191 

 

--
Regards,
Munish Kalra

http://kalraonaspnet.blogspot.com
http://munishmca.co.nr