I am starting to get the hang of this. The trick is in
the ConvertToXML UDF. Here is the xml string that I want
to create which will work with openxml
<ROOT><Worktable rownum="1" fName="Joe" lName="Smith"
DOB="1/1/01" Age="3"/></ROOT>
Here is the string that I pass in to the ConvertToXML UDF
'1,Joe,Smith,1/1/01,3'
Here is the Original function
CREATE FUNCTION dbo.ArrayToXML( @.InputStr varchar (8000),
@.Delim varchar (5))
returns varchar (8000)
as
begin
return ('<ROOT><Worktable Value="' + replace (@.InputStr,
@.Delim, '"/><Worktable Value="') + '"/></ROOT>')
end
which yields the following from '1,Joe,Smith,1/1/01,3'
<ROOT><Worktable Value="1"/><Worktable
Value="Joe"/><Worktable Value="Smith"/><Worktable
Value="1/1/01"/><Worktable Value="3"/></ROOT>
which is 5 rows and one column
But I need it to look like this:
<ROOT><Worktable rownum="1" fName="Joe" lName="Smith"
DOB="1/1/01" Age="3"/></ROOT>
to get 1 row and 5 columns
so in the function my kludge would look like this:
...
declare @.rownum int
declare @.fName varchar(10)
declare @.lName varchar(10)
declare @.dob datetime
declare @.age int
declare @.pos1 int
declare @.pos2 int
begin
--@.input = '1,Joe,Smith,1/1/01,3'
set @.rownum = Left(@.input, 1)
set @.pos1 = charindex(@.input, ',', 3) -- start after 1st
comma so @.pos1 is at 2nd comma
set @.fName = substring(@.input, 3, @.pos1 - 3)
set @.pos1 = @.pos1 + 1 --move 1 past 2nd comma
set @.pos2 = charindex(@.input, ',', @.pos1) --3rd comma
set @.lName = substring(@.input, @.pos1, @.pos2 - @.pos1)
set @.pos2 = @.pos2 + 1 --move 1 past 3rd comma
set @.pos1 = charindex(@.input, ',', @.pos2) --4th comma
set @.dob = substring(@.input, @.pos2, @.pos1 - @.pos2)
set @.age = substring(@.input, @.pos1, Len(@.input - @.pos1))
return '<ROOT><Worktable rownum="' + @.rownum + ' fName="'
+ @.fName + ' lName="' + @.lName + ' dob="' + @.dob + ' age="
+ @.age + '/><ROOT>
End
If anyone has a suggestion how I could streamline this -
please share because my actual row is more like 50
fields. I am thinking a loop would be more efficient, but
I don't know how to implement a loop in a Sql Server UDF.
Could someone share please?
Thanks,
Ed
set @.pos1 = charindex(@.input, ',', @.pos2) --
Hi Ed,
You might have a slightly easier time converting the input string to XML
using C#/VB (or some other mid-tier programming language). Also from a DB
performance standpoint offloading all this string manipulation elsewhere may
help (although the XML you send over the wire will be bigger) - of course
all this depends on how your app is set up and where your bottlenecks are,
so take this with a grain of salt
You can check out
http://msdn.microsoft.com/library/de...chtutorial.asp
for a good example of implementing a class that supports the foreach loop
construct over an array of string tokens. Combine that with your favorite
TextReader implementation to read through all the "rows" of string tokens to
generate your XML. In terms of generating the XML you can just use string
manipulation or the XmlWriter API
(http://msdn.microsoft.com/library/de...-us/cpref/html
/frlrfsystemxmlxmlwriterclasstopic.asp) - The XmlWriter API takes a little
getting used to but it gives you a higher level abstraction write access to
the underlying XML.
You could loop through your string tokens and create XML that looks like:
<Root value1='{somevalue}' value2='{someothervalue}...</Root> by appending
the iteration number to the name of the attribute value - if there is a
consistent ordering of fields in the file you could then just use the
"colPattern" construct in OPENXML to map the attributes to the appropriate
columns in the table.
If you need to do the whole thing in the server let me know and we can think
about further.
Thanks,
Adam Wiener [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"ED" <anonymous@.discussions.microsoft.com> wrote in message
news:0c9701c47b08$e66b4100$a601280a@.phx.gbl...
> I am starting to get the hang of this. The trick is in
> the ConvertToXML UDF. Here is the xml string that I want
> to create which will work with openxml
> <ROOT><Worktable rownum="1" fName="Joe" lName="Smith"
> DOB="1/1/01" Age="3"/></ROOT>
> Here is the string that I pass in to the ConvertToXML UDF
> '1,Joe,Smith,1/1/01,3'
> Here is the Original function
> ----
> CREATE FUNCTION dbo.ArrayToXML( @.InputStr varchar (8000),
> @.Delim varchar (5))
> returns varchar (8000)
> as
> begin
> return ('<ROOT><Worktable Value="' + replace (@.InputStr,
> @.Delim, '"/><Worktable Value="') + '"/></ROOT>')
> end
> which yields the following from '1,Joe,Smith,1/1/01,3'
> <ROOT><Worktable Value="1"/><Worktable
> Value="Joe"/><Worktable Value="Smith"/><Worktable
> Value="1/1/01"/><Worktable Value="3"/></ROOT>
> which is 5 rows and one column
> ----
> But I need it to look like this:
> <ROOT><Worktable rownum="1" fName="Joe" lName="Smith"
> DOB="1/1/01" Age="3"/></ROOT>
> to get 1 row and 5 columns
> so in the function my kludge would look like this:
> ...
> declare @.rownum int
> declare @.fName varchar(10)
> declare @.lName varchar(10)
> declare @.dob datetime
> declare @.age int
> declare @.pos1 int
> declare @.pos2 int
> begin
> --@.input = '1,Joe,Smith,1/1/01,3'
> set @.rownum = Left(@.input, 1)
> set @.pos1 = charindex(@.input, ',', 3) -- start after 1st
> comma so @.pos1 is at 2nd comma
> set @.fName = substring(@.input, 3, @.pos1 - 3)
> set @.pos1 = @.pos1 + 1 --move 1 past 2nd comma
> set @.pos2 = charindex(@.input, ',', @.pos1) --3rd comma
> set @.lName = substring(@.input, @.pos1, @.pos2 - @.pos1)
> set @.pos2 = @.pos2 + 1 --move 1 past 3rd comma
> set @.pos1 = charindex(@.input, ',', @.pos2) --4th comma
> set @.dob = substring(@.input, @.pos2, @.pos1 - @.pos2)
> set @.age = substring(@.input, @.pos1, Len(@.input - @.pos1))
> return '<ROOT><Worktable rownum="' + @.rownum + ' fName="'
> + @.fName + ' lName="' + @.lName + ' dob="' + @.dob + ' age="
> + @.age + '/><ROOT>
> End
> If anyone has a suggestion how I could streamline this -
> please share because my actual row is more like 50
> fields. I am thinking a loop would be more efficient, but
> I don't know how to implement a loop in a Sql Server UDF.
> Could someone share please?
> Thanks,
> Ed
> set @.pos1 = charindex(@.input, ',', @.pos2) --
>
>
|||Actually I was just looking around MSDN and if you can do it using C# then
you will have a much easier time with Chris Lovett's XmlCsvReader at
http://msdn.microsoft.com/library/de...lcsvreader.asp -
This will give you a much easier time - just put the column names you want
in the first row of your CSV file/stream and this thing will do the work for
you. The link has a code sample to get you going.
Thanks,
Adam Wiener [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Adam Wiener [MSFT]" <adamw@.online.microsoft.com> wrote in message
news:ODVQrK7fEHA.636@.TK2MSFTNGP12.phx.gbl...
> Hi Ed,
> You might have a slightly easier time converting the input string to XML
> using C#/VB (or some other mid-tier programming language). Also from a DB
> performance standpoint offloading all this string manipulation elsewhere
may
> help (although the XML you send over the wire will be bigger) - of course
> all this depends on how your app is set up and where your bottlenecks are,
> so take this with a grain of salt
> You can check out
>
http://msdn.microsoft.com/library/de...chtutorial.asp
> for a good example of implementing a class that supports the foreach loop
> construct over an array of string tokens. Combine that with your favorite
> TextReader implementation to read through all the "rows" of string tokens
to
> generate your XML. In terms of generating the XML you can just use string
> manipulation or the XmlWriter API
>
(http://msdn.microsoft.com/library/de...-us/cpref/html
> /frlrfsystemxmlxmlwriterclasstopic.asp) - The XmlWriter API takes a little
> getting used to but it gives you a higher level abstraction write access
to
> the underlying XML.
> You could loop through your string tokens and create XML that looks like:
> <Root value1='{somevalue}' value2='{someothervalue}...</Root> by
appending
> the iteration number to the name of the attribute value - if there is a
> consistent ordering of fields in the file you could then just use the
> "colPattern" construct in OPENXML to map the attributes to the appropriate
> columns in the table.
> If you need to do the whole thing in the server let me know and we can
think
> about further.
> --
> Thanks,
> Adam Wiener [MSFT]
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "ED" <anonymous@.discussions.microsoft.com> wrote in message
> news:0c9701c47b08$e66b4100$a601280a@.phx.gbl...
>
|||Depending on what you finally want to do with it, I am not sure that going
from CSV to XML to rowset is the best way.
At least in the context of SQL Server 2005, you probably want to write a CLR
user defined function that takes the CSV and makes it directly into a
table-valued result.
Otherwise, Adam's suggestions are useful too.
Best regards
Michael
"Adam Wiener [MSFT]" <adamw@.online.microsoft.com> wrote in message
news:uvUdwO7fEHA.536@.TK2MSFTNGP11.phx.gbl...
> Actually I was just looking around MSDN and if you can do it using C# then
> you will have a much easier time with Chris Lovett's XmlCsvReader at
> http://msdn.microsoft.com/library/de...lcsvreader.asp -
> This will give you a much easier time - just put the column names you want
> in the first row of your CSV file/stream and this thing will do the work
> for
> you. The link has a code sample to get you going.
> --
> Thanks,
> Adam Wiener [MSFT]
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Adam Wiener [MSFT]" <adamw@.online.microsoft.com> wrote in message
> news:ODVQrK7fEHA.636@.TK2MSFTNGP12.phx.gbl...
> may
> http://msdn.microsoft.com/library/de...chtutorial.asp
> to
> (http://msdn.microsoft.com/library/de...-us/cpref/html
> to
> appending
> think
> rights.
>
jwelch wrote: