can openxml write multiple fields - 1 row?
From: Ed (anonymous_at_discussions.microsoft.com)
Date: 08/04/04
- Next message: Michael Rys [MSFT]: "Re: Extended Stored Procedure"
- Previous message: Michael Rys [MSFT]: "Re: for auto question"
- Next in thread: Ed: "can openxml write multiple fields - 1 row?"
- Reply: Ed: "can openxml write multiple fields - 1 row?"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 4 Aug 2004 11:59:16 -0700
Hello,
I got an xml routine from the Sql Server NG where I can
pass a string of data (comma delimited) from an outside
source (like a VB app) to a sql server sp, convert the
data to an xml doc, and then use openxml to write this
data to a table. This routine only generates one int
field and generates as many rows as there are comma
delimiters in the string. I would like to know if it is
possible to create the xml doc such that it produces one
row of data with multiple fields (multiple datatypes).
Here is an sp that takes a string arg, uses the xmlconvert
UDF and then calls openxml:
Say I pass in "1,2,3"
exec stp_xmlTest "1,2,3"
-----------------------------------------------
CREATE PROCEDURE [stp_xmlTest]
@xml varchar(8000)
AS
SET NOCOUNT ON
Declare @iDoc Int
Set @xml=dbo.ConvertToXML(@xml, ',')
EXEC sp_xml_preparedocument @iDoc output, @xml
SELECT Value
INTO tmp
FROM OPENXML(@iDoc, '/ROOT/Worktable', 1)
WITH ([Value] INT '@Value')
EXEC sp_xml_removedocument @iDoc
GO
----------------------------------------------------
----------------------------------------------------
CREATE FUNCTION dbo.ConvertToXML( @InputStr varchar
(8000), @Delim varchar (5))
returns varchar (8000)
as
begin
return ('<ROOT><Worktable Value="'
+ replace (@InputStr, @Delim, '"/><Worktable Value="')
+ '"/></ROOT>')
end
------------------------------------------------------
this yields
1
2
3
Instead of Select Into tmp, I would like to Insert Into
tbl1, values(int, varchar, varchar, date, int)
where tbl1 contains the fields (rownum, fName, lName,
bDate, Age). How could I modify the convert function and
sp above to accomplish this? Actually, I think I can
answer part of this: I would declare a variable for each
field
Declare @rownum int,
declare @fName varchar(50),
declare @lName varchar(50),
declare @DOB datetime,
declare @Age int
So how would I modify my ConvertToXML UDF and the call to
openxml so that I Insert this data into one row? How do I
parse the string in ConvertToXML to get only one row? And
in the openxml function
FROM OPENXML(@rownum, @fName, @lName, @DOB,
@Age, '/ROOT/Worktable', 1)
WITH ([Value] INT '@Value')
Let's see, I am guessing that I could have @value1,
@value2, @value3, @value4, @value5
Insert Into tbl1(value1, value2, value3, value4, value5)
Select -- am I close here?
FROM OPENXML(@rownum, @fName, @lName, @DOB,
@Age, '/ROOT/Worktable', 1)
WITH ([value1] INT '@value1', [value2] varchar
(50) '@value2', [value3] varchar(50), '@value3', [value4]
datetime '@value4', [value5] INT '@value5')
And in the ConvertToXML function
return ('<ROOT><Worktable Value="'
+ replace (@InputStr, @Delim, '"/><Worktable Value="')
+ '"/></ROOT>')
would be
return ('<ROOT><Worktable value1="'
+ replace (@InputStr, @Delim, '"/><Worktable value1="')
+ '"/></ROOT>')
--I'm lost now, how do I parse the InputStr to get value1,
value2, ... value5 all on one row? How about the sp? can
I do an Insert Into tbl1(...) Select From OpenXML...?
Thanks,
Ed
- Next message: Michael Rys [MSFT]: "Re: Extended Stored Procedure"
- Previous message: Michael Rys [MSFT]: "Re: for auto question"
- Next in thread: Ed: "can openxml write multiple fields - 1 row?"
- Reply: Ed: "can openxml write multiple fields - 1 row?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
