Using XML as a parameter in SQL2005 1

[kav123]

Could anyone tell me more about working with OpenXML.
I have the following XML structure, which will be passed as a parameter to the stored procedure. I have used OpenXML, but none of the records are being inserted. Could anyone suggest anything else.
The following is just an example of the xml i will be passing as a parameter. The stored procedure compiles fine and executes fine as well... but don't think it is doing anything.


CREATE Procedure <ProceName>
@xmlDAT ntext/xml(Would the type make any difference?)

Declare @xmlPointer int

SELECT @xmlDAT = N'<ROOT><Name ID="IDNumber" Name="SomeName"><Node1 dateTime='29/04/2009' currentvalue='52'/><Node1 dateTime='29/04/2009' currentvalue='52'/><Node1 dateTime='29/04/2009' currentvalue='52'/><Node1 dateTime='29/04/2009' currentvalue='52'/><Node1 dateTime='29/04/2009' currentvalue='52'/></Name></ROOT>
EXEC sp_xml_preparedocument @xmlPointer, @xmlDat

BEGIN
INSERT Table(Id) SELECT * FROM OpenXML (@xmlPointer, N'/PMACData/Name') WITH (Id varchar(50) @ID)
INSERT Table(DateTime, [Val]) SELECT * FROM OpenXML (@xmlPointer,N'//Node1[@dateTime]') WITH (dateTime DateTime '../@dateTime', [Val] real '../@currentvalue')


END

 

[Joulius]

If you're using SQL2000, the correct syntax would be:

CREATE PROCEDURE <ProcName>
	@xmlDAT ntext
AS
BEGIN
	DECLARE @xmlPointer int

	SELECT @xmlDAT = '<ROOT><Name ID="IDNumber" Name="SomeName">
	<Node1 dateTime="29/04/2009" currentvalue="52"/>
	<Node1 dateTime="29/04/2009" currentvalue="52"/>
	<Node1 dateTime="29/04/2009" currentvalue="52"/>
	<Node1 dateTime="29/04/2009" currentvalue="52"/>
	<Node1 dateTime="29/04/2009" currentvalue="52"/>
	</Name></ROOT>'

	EXEC sp_xml_preparedocument @xmlPointer OUTPUT, @xmlDAT

	INSERT INTO TABLE_NAME (Id)
	SELECT Id
	FROM OpenXML (@xmlPointer, N'//Name', 1)
	WITH (Id varchar(50) '@ID')
    
	INSERT INTO TABLE_NAME ([DateTime], [Val])
	SELECT [DateTime], [Val]
	FROM OpenXML (@xmlPointer, N'//Node1', 1)
	WITH
	(
		[DateTime] DateTime '@dateTime',
		[Val] real '@currentvalue'
	)
END

if you're using SQL2005, the correct one would be:

CREATE PROCEDURE <ProcName>
	@xmlDAT xml
AS
BEGIN

	SELECT @xmlDAT = '<ROOT><Name ID="IDNumber" Name="SomeName">
	<Node1 dateTime="29/04/2009" currentvalue="52"/>
	<Node1 dateTime="29/04/2009" currentvalue="52"/>
	<Node1 dateTime="29/04/2009" currentvalue="52"/>
	<Node1 dateTime="29/04/2009" currentvalue="52"/>
	<Node1 dateTime="29/04/2009" currentvalue="52"/>
	</Name></ROOT>'

	INSERT INTO TABLE_NAME (Id)
	SELECT N.C.value('@ID', 'varchar(50)')
	FROM @xmlDAT.nodes('//Name') as N(C)
    
	INSERT INTO TABLE_NAME ([DateTime], [Val])
	SELECT	N.C.value('@dateTime', 'datetime'),
		N.C.value('@currentvalue', 'real')
	FROM @xmlDAT.nodes('//Node1') as N(C)
 
END

Books Online have very good examples on both scenarios.
Hope this helps.

 

[kav123]

Thanks for that, will try that one. I am using 2005. Does that mean that we do not use sp_xml_preparedocument anymore??

 

[Joulius]

That's right. SQL2005 has a xml data type and query and manipulation methods for it.
Look for "xml data type [SQL Server]" in Books Online.

 

[kav123]

Did not make any difference tried both the syntaxes. I am trying to insert all these attributes into the columns of the same table. Could anyone point me towards a good site which could provide me a good understanding of parsing this XML in SQL or if someone here could do a bit of explaining here itself, that would be great. Haven't found any good articles in the web so far.

 

[kav123]

If i use ntext instead of XML datatype, the reason is because of restrictions in the DAL layers which we use to call and execute these stored procedures, do not support this datatype yet.

 

[Joulius]

You can cast the ntext input parameter to xml.

CREATE PROCEDURE <ProcName>
@xmlDAT ntext
AS
BEGIN
DECLARE @xmlData xml
SET @xmlData = CAST(@xmlDAT as xml)
...

 

[kav123]

Hello, I am getting nowhere with this, the records are not being inserted in the database table. Anymore ideas. Basically i need to insert huge chunk of data(we are talking 100s of rows here from a service process.) I thought the best way was to pass the data in an XML and then parse it. But the records are not being inserted. What am i doing wrong.

 

[tsuji]

[0] On the face of it (xmlDAT), Name's attribute of interest Id acts more like a foreign key to the table in question. In any case, insert into on two lines seems making Id entry independently, so the rest column be null. That should not be the anticipated outcome, except you really want it that way?

[1] This is how it gets done (with slight change in data to make them more identifiable).
[tt]
SELECT @xmlDAT = '<ROOT><Name ID="IDNumber" Name="SomeName">
<Node1 dateTime="29/04/2009" currentvalue="52"/>
<Node1 dateTime="28/04/2009" currentvalue="51"/>
<Node1 dateTime="27/04/2009" currentvalue="50"/>
<Node1 dateTime="26/04/2009" currentvalue="49"/>
<Node1 dateTime="25/04/2009" currentvalue="48"/>
</Name></ROOT>'

DECLARE @i int
SET @i=1
WHILE (@xmlDAT.exist('/ROOT/Name/Node1[sql:variable("@i")]')=1) BEGIN
INSERT INTO [blue]tblname[/blue] ([Id],[DateTime],[Val])
SELECT nref.value('@ID','varchar(50)'),
nref.value('Node1[sql:variable("@i")][1]/@dateTime','datetime'),
nref.value('Node1[sql:variable("@i")][1]/@currentvalue','real')
FROM @xmlDAT.nodes('/ROOT/Name') as P(nref)
SET @i=@i+1
END
[/tt]
[1.1] I am not exactly sure the admissibility of datetime here. It cannot be determined with the info shown. In case it gets error out, change it to varchar(10) or alike just to test the logic.

 

[kav123]

Thanks for that, the columns after the Insert into are the actual columns in the table. The ID is not a foreign key. It is just named that way for some reason. This is just a holding table which holds the huge chunk of data.

Thanks for that, shall give it a go... finger crossed!!!

 

[kav123]

Cracked it!!! Thanks guys for all your help. Here is the solution incase any one needs it in the future..


Declare @xmlPointer int,


INSERT INTO Table_Name(ID, SampleDateTime, Value) Select * From OpenXML(@xmlPointer,'//Node1',1) WITH (ID varchar(50) '../@ID',SamlpleDateTime DateTime '@dateTime', Value real '@currentValue')

Nothing complex but hope this is helpful