Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Is there a way to convert ms sql 2008 tables to XML files?

Reply
Thread Tools

Is there a way to convert ms sql 2008 tables to XML files?

 
 
Eric S
Guest
Posts: n/a
 
      06-12-2009
Hi All,

Is there a way to convert ms sql 2008 tables to XML files?
Say I have several tables related to each other and I want to create the
hierarchy of the data such as parent child within the XML Any hidden class
somewhere
to perform the above?

These are small tables where I store the data of menu options and sub
options etc...

Thanks,

Eric

 
Reply With Quote
 
 
 
 
Eric S
Guest
Posts: n/a
 
      06-12-2009
Thanks Juan.

"Juan T. Llibre" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> re:
> !> Is there a way to convert ms sql 2008 tables to XML files?
>
> Create a stored procedure which contains the select statement you wish to
> use.
>
> Make sure that the select statements creates the XML data
> the way you want it, using the FOR XML AUTO clause.
>
> Then use System.IO to write the returned data to a file.
>
>
>
>
> Juan T. Llibre, asp.net MVP
> ¿ Estas probando VS 2010 y ASP.NET 4.0 ?
> Regístrate (gratis) en los Foros de VS 2010 y ASP.NET 4.0, en español
> http://asp.net.do/foros/forums/
> ================================================== ===
> "Eric S" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> Hi All,
>>
>> Is there a way to convert ms sql 2008 tables to XML files?
>> Say I have several tables related to each other and I want to create the
>> hierarchy of the data such as parent child within the XML Any hidden
>> class somewhere
>> to perform the above?
>>
>> These are small tables where I store the data of menu options and sub
>> options etc...
>>
>> Thanks,
>>
>> Eric

>
>
>


 
Reply With Quote
 
 
 
 
Gregory A. Beamer
Guest
Posts: n/a
 
      06-15-2009
"Eric S" <(E-Mail Removed)> wrote in
news:#(E-Mail Removed):

> Hi All,
>
> Is there a way to convert ms sql 2008 tables to XML files?
> Say I have several tables related to each other and I want to create
> the
> hierarchy of the data such as parent child within the XML Any hidden
> class somewhere
> to perform the above?
>
> These are small tables where I store the data of menu options and sub
> options etc...
>
> Thanks,
>
> Eric
>



In addition to Juan's suggestion, which outputs the query as XML, you
can also stream out of the DataSet into XML. Juan's suggestion is best
if you are only going to use XML. If you desire an implementation that
does both a DataSet and XML (even if on different "pages"), use the
second option (or have two different sprocs, but that is more
maintenance).


--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************
 
Reply With Quote
 
Eric S
Guest
Posts: n/a
 
      06-15-2009
Hi Gregory,

Thanks for your reply. I have 3 tables like Parent, Child, Grand Child where
I store the menu and sub menus defintions. The idea is to save it to XML so
it can be used for the web site, so the hierarchy of the relations should be
created based on that as well.

Do you have sample code for the second option?

Thanks,

Eric

>> Hi All,
>>
>> Is there a way to convert ms sql 2008 tables to XML files?
>> Say I have several tables related to each other and I want to create
>> the
>> hierarchy of the data such as parent child within the XML Any hidden
>> class somewhere
>> to perform the above?
>>
>> These are small tables where I store the data of menu options and sub
>> options etc...
>>
>> Thanks,
>>
>> Eric
>>

>
>
> In addition to Juan's suggestion, which outputs the query as XML, you
> can also stream out of the DataSet into XML. Juan's suggestion is best
> if you are only going to use XML. If you desire an implementation that
> does both a DataSet and XML (even if on different "pages"), use the
> second option (or have two different sprocs, but that is more
> maintenance).
>
>
> --
> Gregory A. Beamer
> MVP; MCP: +I, SE, SD, DBA
>
> Twitter: @gbworld
> Blog: http://gregorybeamer.spaces.live.com
>
> *******************************************
> | Think outside the box! |
> *******************************************


 
Reply With Quote
 
Gregory A. Beamer
Guest
Posts: n/a
 
      06-15-2009
"Eric S" <(E-Mail Removed)> wrote in
news:(E-Mail Removed):

> Hi Gregory,
>
> Thanks for your reply. I have 3 tables like Parent, Child, Grand Child
> where I store the menu and sub menus defintions. The idea is to save
> it to XML so it can be used for the web site, so the hierarchy of the
> relations should be created based on that as well.
>
> Do you have sample code for the second option?



I will have to look. I did a lot of working with streams early on, so I
might. I will see if I have any saved code at home on this.

The basics, off the top of my head are:

MemoryStream memStream = new MemoryStream();
dataSet.WriteXml(memStream);
StreamReader reader = new StreamReader(memStream);
string xmlString = reader.ReadToEnd();

you may have to tweak the code a bit to get it working. you can then
feed the string into an XML document to manipulate, save, etc.

Hope this helps!

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************
 
Reply With Quote
 
Eric S
Guest
Posts: n/a
 
      06-15-2009
Hi Gregory,

Thanks for your help, and if you find the code that will be nice if it is
posted in this news group.

Regards,

Eric


>> Hi Gregory,
>>
>> Thanks for your reply. I have 3 tables like Parent, Child, Grand Child
>> where I store the menu and sub menus defintions. The idea is to save
>> it to XML so it can be used for the web site, so the hierarchy of the
>> relations should be created based on that as well.
>>
>> Do you have sample code for the second option?

>
>
> I will have to look. I did a lot of working with streams early on, so I
> might. I will see if I have any saved code at home on this.
>
> The basics, off the top of my head are:
>
> MemoryStream memStream = new MemoryStream();
> dataSet.WriteXml(memStream);
> StreamReader reader = new StreamReader(memStream);
> string xmlString = reader.ReadToEnd();
>
> you may have to tweak the code a bit to get it working. you can then
> feed the string into an XML document to manipulate, save, etc.
>
> Hope this helps!
>
> --
> Gregory A. Beamer
> MVP; MCP: +I, SE, SD, DBA
>
> Twitter: @gbworld
> Blog: http://gregorybeamer.spaces.live.com
>
> *******************************************
> | Think outside the box! |
> *******************************************


 
Reply With Quote
 
Eric S
Guest
Posts: n/a
 
      06-16-2009
Hi Juan,

When I ran the SQL Connection to the ms sql 2008, Somehow I am loosing
Courage Return when I get actual string from the SP and the XML string is
broken. Otherwise within the ms sql server the XML string is just fine.

Any idea of XML functions to be used with that? Or what did I miss?

Thanks,

Eric


"Juan T. Llibre" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> re:
> !> Is there a way to convert ms sql 2008 tables to XML files?
>
> Create a stored procedure which contains the select statement you wish to
> use.
>
> Make sure that the select statements creates the XML data
> the way you want it, using the FOR XML AUTO clause.
>
> Then use System.IO to write the returned data to a file.
>
>
>
>
> Juan T. Llibre, asp.net MVP
> ¿ Estas probando VS 2010 y ASP.NET 4.0 ?
> Regístrate (gratis) en los Foros de VS 2010 y ASP.NET 4.0, en español
> http://asp.net.do/foros/forums/
> ================================================== ===
> "Eric S" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> Hi All,
>>
>> Is there a way to convert ms sql 2008 tables to XML files?
>> Say I have several tables related to each other and I want to create the
>> hierarchy of the data such as parent child within the XML Any hidden
>> class somewhere
>> to perform the above?
>>
>> These are small tables where I store the data of menu options and sub
>> options etc...
>>
>> Thanks,
>>
>> Eric

>
>
>


 
Reply With Quote
 
Juan T. Llibre
Guest
Posts: n/a
 
      06-16-2009
re:
!> Somehow I am loosing Courage Return when I get
!> actual string from the SP and the XML string is broken
!> Any idea of XML functions to be used with that?

Use the replace function in the SQL string to change the carriage return to <br> :

REPLACE(columname, CHAR(13) + CHAR(10), '<br>')




Juan T. Llibre, asp.net MVP
¿ Estas probando VS 2010 y ASP.NET 4.0 ?
Regístrate (gratis) en los Foros de VS 2010 y ASP.NET 4.0, en español
http://asp.net.do/foros/forums/
================================================== ===
"Eric S" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> Hi Juan,
>
> When I ran the SQL Connection to the ms sql 2008, Somehow I am loosing Courage Return when I get actual string from
> the SP and the XML string is broken. Otherwise within the ms sql server the XML string is just fine.
>
> Any idea of XML functions to be used with that? Or what did I miss?
>
> Thanks,
>
> Eric
>
>
> "Juan T. Llibre" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
>> re:
>> !> Is there a way to convert ms sql 2008 tables to XML files?
>>
>> Create a stored procedure which contains the select statement you wish to use.
>>
>> Make sure that the select statements creates the XML data
>> the way you want it, using the FOR XML AUTO clause.
>>
>> Then use System.IO to write the returned data to a file.
>>
>>
>>
>>
>> Juan T. Llibre, asp.net MVP
>> ¿ Estas probando VS 2010 y ASP.NET 4.0 ?
>> Regístrate (gratis) en los Foros de VS 2010 y ASP.NET 4.0, en español
>> http://asp.net.do/foros/forums/
>> ================================================== ===
>> "Eric S" <(E-Mail Removed)> wrote in message news:%(E-Mail Removed)...
>>> Hi All,
>>>
>>> Is there a way to convert ms sql 2008 tables to XML files?
>>> Say I have several tables related to each other and I want to create the
>>> hierarchy of the data such as parent child within the XML Any hidden class somewhere
>>> to perform the above?
>>>
>>> These are small tables where I store the data of menu options and sub
>>> options etc...
>>>
>>> Thanks,
>>>
>>> Eric

>>
>>
>>

>




 
Reply With Quote
 
Eric S
Guest
Posts: n/a
 
      06-17-2009
Hi Juan,

I was wrong in terms the carriage return .
I am using vb.net and ran the code of the ExecuteScalar(cmd).
I have the 2 XML files the bad and the good which are below:

What is that I am missing?

Thanks,

Eric


The below is the string of the XML that I am getting:

<services><dbo.ServiceOptions ServiceOption="Verification Services"
DisplayOptionOrder="1"><dbo.SearchOptions SearchOption="Reverse Phone
Search" DisplayOptionOrder="1"><dbo.SearchCategories SearchCategory="Search
by Phone Number" DisplayOptionOrder="1"
Comment="{\rtf1\fbidis\ansi\ansicpg1252\deff0\defl ang1033{\fonttbl{\f0\fswiss\fprq2\fcharset0
Verdana;}{\f1\fnil\fcharset0 Microsoft Sans Serif;}}&#x0D;&#x0A;{\colortbl
;\red0\green0\blue0;}&#x0D;&#x0A;\viewkind4\uc1\pa rd\ltrpar\sa200\sl276\slmult1\f0\fs16
What is a Reverse Phone Lookup?\par&#x0D;&#x0A;Know who is calling your
family with a Reverse Phone search. \par&#x0D;&#x0A;Our report includes
information, when available, associated with \par&#x0D;&#x0A;Residential,
business, unlisted, non-published.\par&#x0D;&#x0A;\pard\ltrpar\cf1
Results\cf0 may include: Callers Name, address, email address, carrier,
Caller\rquote s connection status and
more.\f1\fs17\par&#x0D;&#x0A;}&#x0D;&#x0A;"
Tip="{\rtf1\fbidis\ansi\ansicpg1252\deff0\deflang1 033{\fonttbl{\f0\fswiss\fprq2\fcharset0
Verdana;}{\f1\fnil\fcharset0 Microsoft Sans
Serif;}}&#x0D;&#x0A;\viewkind4\uc1\pard\ltrpar\sa2 00\sl276\slmult1\f0\fs16
1. Please include an area code with the 7 digit phone number, for the most
accurate results.\par&#x0D;&#x0A;\pard\ltrpar 2. You may enter an old or
disconnected phone number to a find a detailed history of anyone\rquote s
Phone records and personal
history.\f1\fs17\par&#x0D;&#x0A;}&#x0D;&#x0A;"/></dbo.SearchOptions></dbo.ServiceOptions><dbo.ServiceOptions
ServiceOption="Information Services"
DisplayOptionOrder="2"><dbo.SearchOptions SearchOption="People Search"
DisplayOptionOrder="1"><dbo.SearchCategories SearchCategory="Name"
DisplayOptionOrder="1"
Comment="{\rtf1\ansi\deff0{\fonttbl{\f0\fswiss\fpr q2\fcharset0
Verdana;}{\f1\fnil Arial Unicode MS;}}&#x0D;&#x0A;{\*\generator Msftedit
5.41.21.2508;}\viewkind4\uc1\pard\sa200\sl276\slmu lt1\lang1033\f0\fs16 More
Search Products! \line\line Email Search Employment Trace Reverse
Phone/Cell Search \par&#x0D

Here is the actual string copied from the ms sql 2008 which works fine;

<services>
<dbo.ServiceOptions ServiceOption="Verification Services"
DisplayOptionOrder="1">
<dbo.SearchOptions SearchOption="Reverse Phone Search"
DisplayOptionOrder="1">
<dbo.SearchCategories SearchCategory="Search by Phone Number"
DisplayOptionOrder="1"
Comment="{\rtf1\fbidis\ansi\ansicpg1252\deff0\defl ang1033{\fonttbl{\f0\fswiss\fprq2\fcharset0
Verdana;}{\f1\fnil\fcharset0 Microsoft Sans Serif;}}&#xD;&#xA;{\colortbl
;\red0\green0\blue0;}&#xD;&#xA;\viewkind4\uc1\pard \ltrpar\sa200\sl276\slmult1\f0\fs16
What is a Reverse Phone Lookup?\par&#xD;&#xA;Know who is calling your family
with a Reverse Phone search. \par&#xD;&#xA;Our report includes information,
when available, associated with \par&#xD;&#xA;Residential, business,
unlisted, non-published.\par&#xD;&#xA;\pard\ltrpar\cf1 Results\cf0 may
include: Callers Name, address, email address, carrier, Caller\rquote s
connection status and more.\f1\fs17\par&#xD;&#xA;}&#xD;&#xA;"
Tip="{\rtf1\fbidis\ansi\ansicpg1252\deff0\deflang1 033{\fonttbl{\f0\fswiss\fprq2\fcharset0
Verdana;}{\f1\fnil\fcharset0 Microsoft Sans
Serif;}}&#xD;&#xA;\viewkind4\uc1\pard\ltrpar\sa200 \sl276\slmult1\f0\fs16 1.
Please include an area code with the 7 digit phone number, for the most
accurate results.\par&#xD;&#xA;\pard\ltrpar 2. You may enter an old or
disconnected phone number to a find a detailed history of anyone\rquote s
Phone records and personal history.\f1\fs17\par&#xD;&#xA;}&#xD;&#xA;" />
</dbo.SearchOptions>
</dbo.ServiceOptions>
<dbo.ServiceOptions ServiceOption="Information Services"
DisplayOptionOrder="2">
<dbo.SearchOptions SearchOption="People Search" DisplayOptionOrder="1">
<dbo.SearchCategories SearchCategory="Name" DisplayOptionOrder="1"
Comment="{\rtf1\ansi\deff0{\fonttbl{\f0\fswiss\fpr q2\fcharset0
Verdana;}{\f1\fnil Arial Unicode MS;}}&#xD;&#xA;{\*\generator Msftedit
5.41.21.2508;}\viewkind4\uc1\pard\sa200\sl276\slmu lt1\lang1033\f0\fs16 More
Search Products! \line\line Email Search Employment Trace Reverse
Phone/Cell Search \par&#xD;&#xA;Web Site Ownership Social Net Search Sex
Offender\par&#xD;&#xA;Intl People/Business Search Email Lookup
\par&#xD;&#xA;\f1\par&#xD;&#xA;}&#xD;&#xA;&#x0;"
Tip="{\rtf1\ansi\deff0{\fonttbl{\f0\fswiss\fprq2\f charset0
Verdana;}{\f1\fnil Arial Unicode MS;}}&#xD;&#xA;{\*\generator Msftedit
5.41.21.2508;}\viewkind4\uc1\pard\sa200\sl276\slmu lt1\lang1033\f0\fs16 1.
Enter you\rquote re subject\rquote s complete last and first
name\par&#xD;&#xA;2. Filter you\rquote re search by including the state
and/or city fields to narrow down search results.\par&#xD;&#xA;3. Use
Proper/Formal names, limit you\rquote re use of Nick names \line\pard
Example Robert for \ldblquote Bob\rdblquote Richard for \ldblquote
Dick\rdblquote\f1\par&#xD;&#xA;}&#xD;&#xA;&#x0;" />
<dbo.SearchCategories SearchCategory="Address" DisplayOptionOrder="2"
Comment="{\rtf1\fbidis\ansi\ansicpg1252\deff0\defl ang1033{\fonttbl{\f0\fswiss\fprq2\fcharset0
Verdana;}{\f1\fnil\fcharset0 Microsoft Sans
Serif;}}&#xD;&#xA;\viewkind4\uc1\pard\ltrpar\sa200 \sl276\slmult1\f0\fs16
When you're looking for that old friend, and you only remember an old
address, we can help to fill in the blanks with their current address.
Address lookup is also a good tool to identify the current residents of a
specify address
location\par&#xD;&#xA;\pard\ltrpar\f1\fs17\par&#xD ;&#xA;}&#xD;&#xA;"
Tip="{\rtf1\fbidis\ansi\ansicpg1252\deff0\deflang1 033{\fonttbl{\f0\fswiss\fprq2\fcharset0
Verdana;}{\f1\fnil\fcharset0 Microsoft Sans
Serif;}}&#xD;&#xA;\viewkind4\uc1\pard\ltrpar\sa200 \sl276\slmult1\f0\fs16
Include as much of the address as possible, enter a city and Zip code. Zip
codes may vary, if you are not Sure about the Zip code, leave it off and
limit by city.\par&#xD;&#xA;\pard\ltrpar\f1\fs17\par&#xD;&# xA;}&#xD;&#xA;"
/>
<dbo.SearchCategories SearchCategory="Phone" DisplayOptionOrder="3"
Comment="{\rtf1\fbidis\ansi\ansicpg1252\deff0{\fon ttbl{\f0\fswiss\fprq2\fcharset0
Verdana;}{\f1\fnil Arial Unicode
MS;}}&#xD;&#xA;\viewkind4\uc1\pard\ltrpar\sb100\sa 100\sl276\slmult1\lang1033\f0\fs16
Who's Calling? Do you need to know who's on the other end of that phone
number?\par&#xD;&#xA;The report includes information, when available,
associated with residential, business, cell, unlisted, non-published
&quot;Registered Owner(s)&quot; including their Address, Possible Date of
Birth. Options include, search for available public records for that listed
individual\par&#xD;&#xA;\pard\ltrpar\f1\par&#xD;&# xA;}&#xD;&#xA;"
Tip="{\rtf1\fbidis\ansi\ansicpg1252\deff0\deflang1 033{\fonttbl{\f0\fswiss\fprq2\fcharset0
Verdana;}{\f1\fnil\fcharset0 Microsoft Sans
Serif;}}&#xD;&#xA;\viewkind4\uc1\pard\ltrpar\sa200 \sl276\slmult1\f0\fs16 1.
Please include an area code with the 7 digit phone number, for the most
accurate results.\line\line 2. You may enter an old or disconnected phone
number to a find a detailed history of anyone\rquote s Phone records and
personal
history.\par&#xD;&#xA;\pard\ltrpar\f1\fs17\par&#xD ;&#xA;}&#xD;&#xA;" />
<dbo.SearchCategories SearchCategory="Email " DisplayOptionOrder="4"
Comment="{\rtf1\ansi\ansicpg1252\deff0\deflang1033 {\fonttbl{\f0\fswiss\fprq2\fcharset0
Verdana;}{\f1\fnil Arial Unicode MS;}}&#xD;&#xA;{\*\generator Msftedit
5.41.21.2508;}\viewkind4\uc1\pard\sa200\sl276\slmu lt1\f0\fs16 Contact them
immediately!! \line Results may Include: \par&#xD;&#xA;Individuals Name
or Business Name, Street Address, Telephone Numbers, Possible relatives,
Aliases and Maiden names Indentify an individual or Business, enter an email
address for search. \par&#xD;&#xA;\pard\f1\par&#xD;&#xA;}&#xD;&#xA;&#x 0;"
Tip="{\rtf1\fbidis\ansi\ansicpg1252\deff0{\fonttbl {\f0\fswiss\fprq2\fcharset0
Verdana;}}&#xD;&#xA;\viewkind4\uc1\pard\ltrpar\lan g1033\f0\fs16 Enter
\ldblquote Any\rdblquote email address associated with your subject. We can
track older emails dating back to 1999.\par&#xD;&#xA;}&#xD;&#xA;" />
<dbo.SearchCategories SearchCategory="SSN " DisplayOptionOrder="5"
Comment="{\rtf1\fbidis\ansi\ansicpg1252\deff0{\fon ttbl{\f0\fswiss\fprq2\fcharset0
Verdana;}{\f1\fnil Arial Unicode
MS;}}&#xD;&#xA;\viewkind4\uc1\pard\ltrpar\sa200\sl 276\slmult1\lang1033\f0\fs16
What is SSN People Search?\par&#xD;&#xA;Locating People by their SSN is
another method to search for family members, old friends and associates. Our
People Search report includes addresses &amp; phone numbers, relatives &amp;
associates, background options &amp;
more.\par&#xD;&#xA;\pard\ltrpar\f1\par&#xD;&#xA;}& #xD;&#xA;"
Tip="{\rtf1\fbidis\ansi\ansicpg1252\deff0\deflang1 033{\fonttbl{\f0\fswiss\fprq2\fcharset0
Verdana;}{\f1\fnil Arial Unicode
MS;}}&#xD;&#xA;\viewkind4\uc1\pard\ltrpar\sa200\sl 276\slmult1\f0\fs16 1. SSN
based searches must include the person\rquote s complete last name for this
search type.\line\pard\ltrpar 2. Enter the entire social security number
(all 9 digits) partial SSN number are not permitted, and will not allow a
search to be performed\i .\i0\f1\par&#xD;&#xA;}&#xD;&#xA;" />
</dbo.SearchOptions>
<dbo.SearchOptions SearchOption="Email Search" DisplayOptionOrder="2">
<dbo.SearchCategories SearchCategory="Email Search"
DisplayOptionOrder="1"
Comment="{\rtf1\fbidis\ansi\ansicpg1252\deff0\defl ang1033{\fonttbl{\f0\fswiss\fprq2\fcharset0
Verdana;}{\f1\fnil\fcharset0 Microsoft Sans
Serif;}}&#xD;&#xA;\viewkind4\uc1\pard\ltrpar\f0\fs 16 Email Search is a great
way to find email addresses of long-lost friends or associates. Results
include email address, mailing address and possible phone number. Contact
them immediately!! \f1\fs17\par&#xD;&#xA;}&#xD;&#xA;"
Tip="{\rtf1\fbidis\ansi\ansicpg1252\deff0\deflang1 033{\fonttbl{\f0\fswiss\fprq2\fcharset0
Verdana;}{\f1\fnil\fcharset0 Microsoft Sans
Serif;}}&#xD;&#xA;\viewkind4\uc1\pard\ltrpar\sa200 \sl276\slmult1\f0\fs16
Enter you\rquote re subject\rquote s complete Name or Street Address.
\par&#xD;&#xA;\pard\ltrpar\f1\fs17\par&#xD;&#xA;}& #xD;&#xA;" />
<dbo.SearchCategories SearchCategory="Reverse Email"
DisplayOptionOrder="2"
Comment="{\rtf1\fbidis\ansi\ansicpg1252\deff0\defl ang1033{\fonttbl{\f0\fswiss\fprq2\fcharset0
Verdana;}{\f1\fnil\fcharset0 Microsoft Sans
Serif;}}&#xD;&#xA;\viewkind4\uc1\pard\ltrpar\f0\fs 16 With over 800 Million
individual &amp; Business email records, Human Finders is the Largest source
for Email searches in the US, Canada, UK and
Worldwide.\f1\fs17\par&#xD;&#xA;}&#xD;&#xA;"
Tip="{\rtf1\fbidis\ansi\ansicpg1252\deff0\deflang1 033{\fonttbl{\f0\fswiss\fprq2\fcharset0
Verdana;}{\f1\fnil\fcharset0 Microsoft Sans
Serif;}}&#xD;&#xA;\viewkind4\uc1\pard\ltrpar\sa200 \sl276\slmult1\f0\fs16 1.
Enter you\rquote re subject\rquote s complete Name, you may filter
you\rquote re results by city or State. 2.
Use Proper/Formal names, limit you\rquote re use of Nick names
\par&#xD;&#xA;\pard\ltrpar Example Robert for \ldblquote Bob\rdblquote
Richard for \ldblquote Dick\rdblquote\f1\fs17\par&#xD;&#xA;}&#xD;&#xA;" />
</dbo.SearchOptions>
</dbo.ServiceOptions>
</services>



"Juan T. Llibre" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> re:
> !> Somehow I am loosing Courage Return when I get
> !> actual string from the SP and the XML string is broken
> !> Any idea of XML functions to be used with that?
>
> Use the replace function in the SQL string to change the carriage return
> to <br> :
>
> REPLACE(columname, CHAR(13) + CHAR(10), '<br>')
>
>
>
>
> Juan T. Llibre, asp.net MVP
> ¿ Estas probando VS 2010 y ASP.NET 4.0 ?
> Regístrate (gratis) en los Foros de VS 2010 y ASP.NET 4.0, en español
> http://asp.net.do/foros/forums/
> ================================================== ===
> "Eric S" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Hi Juan,
>>
>> When I ran the SQL Connection to the ms sql 2008, Somehow I am loosing
>> Courage Return when I get actual string from the SP and the XML string
>> is broken. Otherwise within the ms sql server the XML string is just
>> fine.
>>
>> Any idea of XML functions to be used with that? Or what did I miss?
>>
>> Thanks,
>>
>> Eric
>>
>>
>> "Juan T. Llibre" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> re:
>>> !> Is there a way to convert ms sql 2008 tables to XML files?
>>>
>>> Create a stored procedure which contains the select statement you wish
>>> to use.
>>>
>>> Make sure that the select statements creates the XML data
>>> the way you want it, using the FOR XML AUTO clause.
>>>
>>> Then use System.IO to write the returned data to a file.
>>>
>>>
>>>
>>>
>>> Juan T. Llibre, asp.net MVP
>>> ¿ Estas probando VS 2010 y ASP.NET 4.0 ?
>>> Regístrate (gratis) en los Foros de VS 2010 y ASP.NET 4.0, en español
>>> http://asp.net.do/foros/forums/
>>> ================================================== ===
>>> "Eric S" <(E-Mail Removed)> wrote in message
>>> news:%(E-Mail Removed)...
>>>> Hi All,
>>>>
>>>> Is there a way to convert ms sql 2008 tables to XML files?
>>>> Say I have several tables related to each other and I want to create
>>>> the
>>>> hierarchy of the data such as parent child within the XML Any hidden
>>>> class somewhere
>>>> to perform the above?
>>>>
>>>> These are small tables where I store the data of menu options and sub
>>>> options etc...
>>>>
>>>> Thanks,
>>>>
>>>> Eric
>>>
>>>
>>>

>>

>
>
>


 
Reply With Quote
 
Eric S
Guest
Posts: n/a
 
      06-17-2009
Hi Juan,

After looking at the code I made it work, since I removed 2 nVARCHAR(MAX)
columns from the SQL statement where it is used as an RTF. Is there any way
to make it work with the nVARCHAR(MAX) data type, or any other field type
that will allow me to work with RTF.

Thanks,

Eric


"Eric S" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi Juan,
>
> I was wrong in terms the carriage return .
> I am using vb.net and ran the code of the ExecuteScalar(cmd).
> I have the 2 XML files the bad and the good which are below:
>
> What is that I am missing?
>
> Thanks,
>
> Eric
>
>
> The below is the string of the XML that I am getting:
>
> <services><dbo.ServiceOptions ServiceOption="Verification Services"
> DisplayOptionOrder="1"><dbo.SearchOptions SearchOption="Reverse Phone
> Search" DisplayOptionOrder="1"><dbo.SearchCategories
> SearchCategory="Search by Phone Number" DisplayOptionOrder="1"
> Comment="{\rtf1\fbidis\ansi\ansicpg1252\deff0\defl ang1033{\fonttbl{\f0\fswiss\fprq2\fcharset0
> Verdana;}{\f1\fnil\fcharset0 Microsoft Sans Serif;}}&#x0D;&#x0A;{\colortbl
> ;\red0\green0\blue0;}&#x0D;&#x0A;\viewkind4\uc1\pa rd\ltrpar\sa200\sl276\slmult1\f0\fs16
> What is a Reverse Phone Lookup?\par&#x0D;&#x0A;Know who is calling your
> family with a Reverse Phone search. \par&#x0D;&#x0A;Our report includes
> information, when available, associated with \par&#x0D;&#x0A;Residential,
> business, unlisted, non-published.\par&#x0D;&#x0A;\pard\ltrpar\cf1
> Results\cf0 may include: Callers Name, address, email address, carrier,
> Caller\rquote s connection status and
> more.\f1\fs17\par&#x0D;&#x0A;}&#x0D;&#x0A;"
> Tip="{\rtf1\fbidis\ansi\ansicpg1252\deff0\deflang1 033{\fonttbl{\f0\fswiss\fprq2\fcharset0
> Verdana;}{\f1\fnil\fcharset0 Microsoft Sans
> Serif;}}&#x0D;&#x0A;\viewkind4\uc1\pard\ltrpar\sa2 00\sl276\slmult1\f0\fs16
> 1. Please include an area code with the 7 digit phone number, for the most
> accurate results.\par&#x0D;&#x0A;\pard\ltrpar 2. You may enter an old or
> disconnected phone number to a find a detailed history of anyone\rquote s
> Phone records and personal
> history.\f1\fs17\par&#x0D;&#x0A;}&#x0D;&#x0A;"/></dbo.SearchOptions></dbo.ServiceOptions><dbo.ServiceOptions
> ServiceOption="Information Services"
> DisplayOptionOrder="2"><dbo.SearchOptions SearchOption="People Search"
> DisplayOptionOrder="1"><dbo.SearchCategories SearchCategory="Name"
> DisplayOptionOrder="1"
> Comment="{\rtf1\ansi\deff0{\fonttbl{\f0\fswiss\fpr q2\fcharset0
> Verdana;}{\f1\fnil Arial Unicode MS;}}&#x0D;&#x0A;{\*\generator Msftedit
> 5.41.21.2508;}\viewkind4\uc1\pard\sa200\sl276\slmu lt1\lang1033\f0\fs16
> More Search Products! \line\line Email Search Employment Trace
> Reverse Phone/Cell Search \par&#x0D
>
> Here is the actual string copied from the ms sql 2008 which works fine;
>
> <services>
> <dbo.ServiceOptions ServiceOption="Verification Services"
> DisplayOptionOrder="1">
> <dbo.SearchOptions SearchOption="Reverse Phone Search"
> DisplayOptionOrder="1">
> <dbo.SearchCategories SearchCategory="Search by Phone Number"
> DisplayOptionOrder="1"
> Comment="{\rtf1\fbidis\ansi\ansicpg1252\deff0\defl ang1033{\fonttbl{\f0\fswiss\fprq2\fcharset0
> Verdana;}{\f1\fnil\fcharset0 Microsoft Sans Serif;}}&#xD;&#xA;{\colortbl
> ;\red0\green0\blue0;}&#xD;&#xA;\viewkind4\uc1\pard \ltrpar\sa200\sl276\slmult1\f0\fs16
> What is a Reverse Phone Lookup?\par&#xD;&#xA;Know who is calling your
> family with a Reverse Phone search. \par&#xD;&#xA;Our report includes
> information, when available, associated with \par&#xD;&#xA;Residential,
> business, unlisted, non-published.\par&#xD;&#xA;\pard\ltrpar\cf1
> Results\cf0 may include: Callers Name, address, email address, carrier,
> Caller\rquote s connection status and
> more.\f1\fs17\par&#xD;&#xA;}&#xD;&#xA;"
> Tip="{\rtf1\fbidis\ansi\ansicpg1252\deff0\deflang1 033{\fonttbl{\f0\fswiss\fprq2\fcharset0
> Verdana;}{\f1\fnil\fcharset0 Microsoft Sans
> Serif;}}&#xD;&#xA;\viewkind4\uc1\pard\ltrpar\sa200 \sl276\slmult1\f0\fs16
> 1. Please include an area code with the 7 digit phone number, for the most
> accurate results.\par&#xD;&#xA;\pard\ltrpar 2. You may enter an old or
> disconnected phone number to a find a detailed history of anyone\rquote s
> Phone records and personal history.\f1\fs17\par&#xD;&#xA;}&#xD;&#xA;" />
> </dbo.SearchOptions>
> </dbo.ServiceOptions>
> <dbo.ServiceOptions ServiceOption="Information Services"
> DisplayOptionOrder="2">
> <dbo.SearchOptions SearchOption="People Search" DisplayOptionOrder="1">
> <dbo.SearchCategories SearchCategory="Name" DisplayOptionOrder="1"
> Comment="{\rtf1\ansi\deff0{\fonttbl{\f0\fswiss\fpr q2\fcharset0
> Verdana;}{\f1\fnil Arial Unicode MS;}}&#xD;&#xA;{\*\generator Msftedit
> 5.41.21.2508;}\viewkind4\uc1\pard\sa200\sl276\slmu lt1\lang1033\f0\fs16
> More Search Products! \line\line Email Search Employment Trace
> Reverse Phone/Cell Search \par&#xD;&#xA;Web Site Ownership Social Net
> Search Sex Offender\par&#xD;&#xA;Intl People/Business Search Email Lookup
> \par&#xD;&#xA;\f1\par&#xD;&#xA;}&#xD;&#xA;&#x0;"
> Tip="{\rtf1\ansi\deff0{\fonttbl{\f0\fswiss\fprq2\f charset0
> Verdana;}{\f1\fnil Arial Unicode MS;}}&#xD;&#xA;{\*\generator Msftedit
> 5.41.21.2508;}\viewkind4\uc1\pard\sa200\sl276\slmu lt1\lang1033\f0\fs16 1.
> Enter you\rquote re subject\rquote s complete last and first
> name\par&#xD;&#xA;2. Filter you\rquote re search by including the state
> and/or city fields to narrow down search results.\par&#xD;&#xA;3. Use
> Proper/Formal names, limit you\rquote re use of Nick names \line\pard
> Example Robert for \ldblquote Bob\rdblquote Richard for \ldblquote
> Dick\rdblquote\f1\par&#xD;&#xA;}&#xD;&#xA;&#x0;" />
> <dbo.SearchCategories SearchCategory="Address" DisplayOptionOrder="2"
> Comment="{\rtf1\fbidis\ansi\ansicpg1252\deff0\defl ang1033{\fonttbl{\f0\fswiss\fprq2\fcharset0
> Verdana;}{\f1\fnil\fcharset0 Microsoft Sans
> Serif;}}&#xD;&#xA;\viewkind4\uc1\pard\ltrpar\sa200 \sl276\slmult1\f0\fs16
> When you're looking for that old friend, and you only remember an old
> address, we can help to fill in the blanks with their current address.
> Address lookup is also a good tool to identify the current residents of a
> specify address
> location\par&#xD;&#xA;\pard\ltrpar\f1\fs17\par&#xD ;&#xA;}&#xD;&#xA;"
> Tip="{\rtf1\fbidis\ansi\ansicpg1252\deff0\deflang1 033{\fonttbl{\f0\fswiss\fprq2\fcharset0
> Verdana;}{\f1\fnil\fcharset0 Microsoft Sans
> Serif;}}&#xD;&#xA;\viewkind4\uc1\pard\ltrpar\sa200 \sl276\slmult1\f0\fs16
> Include as much of the address as possible, enter a city and Zip code. Zip
> codes may vary, if you are not Sure about the Zip code, leave it off and
> limit by city.\par&#xD;&#xA;\pard\ltrpar\f1\fs17\par&#xD;&# xA;}&#xD;&#xA;"
> />
> <dbo.SearchCategories SearchCategory="Phone" DisplayOptionOrder="3"
> Comment="{\rtf1\fbidis\ansi\ansicpg1252\deff0{\fon ttbl{\f0\fswiss\fprq2\fcharset0
> Verdana;}{\f1\fnil Arial Unicode
> MS;}}&#xD;&#xA;\viewkind4\uc1\pard\ltrpar\sb100\sa 100\sl276\slmult1\lang1033\f0\fs16
> Who's Calling? Do you need to know who's on the other end of that phone
> number?\par&#xD;&#xA;The report includes information, when available,
> associated with residential, business, cell, unlisted, non-published
> &quot;Registered Owner(s)&quot; including their Address, Possible Date of
> Birth. Options include, search for available public records for that
> listed individual\par&#xD;&#xA;\pard\ltrpar\f1\par&#xD;&# xA;}&#xD;&#xA;"
> Tip="{\rtf1\fbidis\ansi\ansicpg1252\deff0\deflang1 033{\fonttbl{\f0\fswiss\fprq2\fcharset0
> Verdana;}{\f1\fnil\fcharset0 Microsoft Sans
> Serif;}}&#xD;&#xA;\viewkind4\uc1\pard\ltrpar\sa200 \sl276\slmult1\f0\fs16
> 1. Please include an area code with the 7 digit phone number, for the most
> accurate results.\line\line 2. You may enter an old or disconnected phone
> number to a find a detailed history of anyone\rquote s Phone records
> and personal
> history.\par&#xD;&#xA;\pard\ltrpar\f1\fs17\par&#xD ;&#xA;}&#xD;&#xA;" />
> <dbo.SearchCategories SearchCategory="Email " DisplayOptionOrder="4"
> Comment="{\rtf1\ansi\ansicpg1252\deff0\deflang1033 {\fonttbl{\f0\fswiss\fprq2\fcharset0
> Verdana;}{\f1\fnil Arial Unicode MS;}}&#xD;&#xA;{\*\generator Msftedit
> 5.41.21.2508;}\viewkind4\uc1\pard\sa200\sl276\slmu lt1\f0\fs16 Contact them
> immediately!! \line Results may Include: \par&#xD;&#xA;Individuals
> Name or Business Name, Street Address, Telephone Numbers, Possible
> relatives, Aliases and Maiden names Indentify an individual or Business,
> enter an email address for search.
> \par&#xD;&#xA;\pard\f1\par&#xD;&#xA;}&#xD;&#xA;&#x 0;"
> Tip="{\rtf1\fbidis\ansi\ansicpg1252\deff0{\fonttbl {\f0\fswiss\fprq2\fcharset0
> Verdana;}}&#xD;&#xA;\viewkind4\uc1\pard\ltrpar\lan g1033\f0\fs16 Enter
> \ldblquote Any\rdblquote email address associated with your subject. We
> can track older emails dating back to 1999.\par&#xD;&#xA;}&#xD;&#xA;" />
> <dbo.SearchCategories SearchCategory="SSN " DisplayOptionOrder="5"
> Comment="{\rtf1\fbidis\ansi\ansicpg1252\deff0{\fon ttbl{\f0\fswiss\fprq2\fcharset0
> Verdana;}{\f1\fnil Arial Unicode
> MS;}}&#xD;&#xA;\viewkind4\uc1\pard\ltrpar\sa200\sl 276\slmult1\lang1033\f0\fs16
> What is SSN People Search?\par&#xD;&#xA;Locating People by their SSN is
> another method to search for family members, old friends and associates.
> Our People Search report includes addresses &amp; phone numbers, relatives
> &amp; associates, background options &amp;
> more.\par&#xD;&#xA;\pard\ltrpar\f1\par&#xD;&#xA;}& #xD;&#xA;"
> Tip="{\rtf1\fbidis\ansi\ansicpg1252\deff0\deflang1 033{\fonttbl{\f0\fswiss\fprq2\fcharset0
> Verdana;}{\f1\fnil Arial Unicode
> MS;}}&#xD;&#xA;\viewkind4\uc1\pard\ltrpar\sa200\sl 276\slmult1\f0\fs16 1.
> SSN based searches must include the person\rquote s complete last name for
> this search type.\line\pard\ltrpar 2. Enter the entire social security
> number (all 9 digits) partial SSN number are not permitted, and will not
> allow a search to be performed\i .\i0\f1\par&#xD;&#xA;}&#xD;&#xA;" />
> </dbo.SearchOptions>
> <dbo.SearchOptions SearchOption="Email Search" DisplayOptionOrder="2">
> <dbo.SearchCategories SearchCategory="Email Search"
> DisplayOptionOrder="1"
> Comment="{\rtf1\fbidis\ansi\ansicpg1252\deff0\defl ang1033{\fonttbl{\f0\fswiss\fprq2\fcharset0
> Verdana;}{\f1\fnil\fcharset0 Microsoft Sans
> Serif;}}&#xD;&#xA;\viewkind4\uc1\pard\ltrpar\f0\fs 16 Email Search is a
> great way to find email addresses of long-lost friends or associates.
> Results include email address, mailing address and possible phone number.
> Contact them immediately!! \f1\fs17\par&#xD;&#xA;}&#xD;&#xA;"
> Tip="{\rtf1\fbidis\ansi\ansicpg1252\deff0\deflang1 033{\fonttbl{\f0\fswiss\fprq2\fcharset0
> Verdana;}{\f1\fnil\fcharset0 Microsoft Sans
> Serif;}}&#xD;&#xA;\viewkind4\uc1\pard\ltrpar\sa200 \sl276\slmult1\f0\fs16
> Enter you\rquote re subject\rquote s complete Name or Street Address.
> \par&#xD;&#xA;\pard\ltrpar\f1\fs17\par&#xD;&#xA;}& #xD;&#xA;" />
> <dbo.SearchCategories SearchCategory="Reverse Email"
> DisplayOptionOrder="2"
> Comment="{\rtf1\fbidis\ansi\ansicpg1252\deff0\defl ang1033{\fonttbl{\f0\fswiss\fprq2\fcharset0
> Verdana;}{\f1\fnil\fcharset0 Microsoft Sans
> Serif;}}&#xD;&#xA;\viewkind4\uc1\pard\ltrpar\f0\fs 16 With over 800 Million
> individual &amp; Business email records, Human Finders is the Largest
> source for Email searches in the US, Canada, UK and
> Worldwide.\f1\fs17\par&#xD;&#xA;}&#xD;&#xA;"
> Tip="{\rtf1\fbidis\ansi\ansicpg1252\deff0\deflang1 033{\fonttbl{\f0\fswiss\fprq2\fcharset0
> Verdana;}{\f1\fnil\fcharset0 Microsoft Sans
> Serif;}}&#xD;&#xA;\viewkind4\uc1\pard\ltrpar\sa200 \sl276\slmult1\f0\fs16
> 1. Enter you\rquote re subject\rquote s complete Name, you may filter
> you\rquote re results by city or State. 2.
> Use Proper/Formal names, limit you\rquote re use of Nick names
> \par&#xD;&#xA;\pard\ltrpar Example Robert for \ldblquote Bob\rdblquote
> Richard for \ldblquote Dick\rdblquote\f1\fs17\par&#xD;&#xA;}&#xD;&#xA;" />
> </dbo.SearchOptions>
> </dbo.ServiceOptions>
> </services>
>
>
>
> "Juan T. Llibre" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> re:
>> !> Somehow I am loosing Courage Return when I get
>> !> actual string from the SP and the XML string is broken
>> !> Any idea of XML functions to be used with that?
>>
>> Use the replace function in the SQL string to change the carriage return
>> to <br> :
>>
>> REPLACE(columname, CHAR(13) + CHAR(10), '<br>')
>>
>>
>>
>>
>> Juan T. Llibre, asp.net MVP
>> ¿ Estas probando VS 2010 y ASP.NET 4.0 ?
>> Regístrate (gratis) en los Foros de VS 2010 y ASP.NET 4.0, en español
>> http://asp.net.do/foros/forums/
>> ================================================== ===
>> "Eric S" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> Hi Juan,
>>>
>>> When I ran the SQL Connection to the ms sql 2008, Somehow I am loosing
>>> Courage Return when I get actual string from the SP and the XML string
>>> is broken. Otherwise within the ms sql server the XML string is just
>>> fine.
>>>
>>> Any idea of XML functions to be used with that? Or what did I miss?
>>>
>>> Thanks,
>>>
>>> Eric
>>>
>>>
>>> "Juan T. Llibre" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> re:
>>>> !> Is there a way to convert ms sql 2008 tables to XML files?
>>>>
>>>> Create a stored procedure which contains the select statement you wish
>>>> to use.
>>>>
>>>> Make sure that the select statements creates the XML data
>>>> the way you want it, using the FOR XML AUTO clause.
>>>>
>>>> Then use System.IO to write the returned data to a file.
>>>>
>>>>
>>>>
>>>>
>>>> Juan T. Llibre, asp.net MVP
>>>> ¿ Estas probando VS 2010 y ASP.NET 4.0 ?
>>>> Regístrate (gratis) en los Foros de VS 2010 y ASP.NET 4.0, en español
>>>> http://asp.net.do/foros/forums/
>>>> ================================================== ===
>>>> "Eric S" <(E-Mail Removed)> wrote in message
>>>> news:%(E-Mail Removed)...
>>>>> Hi All,
>>>>>
>>>>> Is there a way to convert ms sql 2008 tables to XML files?
>>>>> Say I have several tables related to each other and I want to create
>>>>> the
>>>>> hierarchy of the data such as parent child within the XML Any hidden
>>>>> class somewhere
>>>>> to perform the above?
>>>>>
>>>>> These are small tables where I store the data of menu options and sub
>>>>> options etc...
>>>>>
>>>>> Thanks,
>>>>>
>>>>> Eric
>>>>
>>>>
>>>>
>>>

>>
>>
>>

>


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Re: Is there a way to convert ms sql 2008 tables to XML files? Juan T. Llibre ASP .Net 0 06-12-2009 02:12 PM
Copy SQL SERVER 2008 database to sql Express 2008 AAaron123 ASP .Net 6 10-28-2008 03:39 PM
Tools to extract data from SQL database and convert it into XML & insert XML data into SQL databases Harry Zoroc XML 1 07-12-2004 10:10 PM



Advertisments