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

From: "Eric S" <NoSpam@xxxxxxxxxxx>
Date: Tue, 16 Jun 2009 21:07:44 -0400

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" <NoSpam@xxxxxxxxxxx> wrote in message news:OH2qUJu7JHA.5008@xxxxxxxxxxxxxxxxxxxxxxx

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\deflang1033{\fonttbl{\f0\fswiss\fprq2\fcharset0 Verdana;}{\f1\fnil\fcharset0 Microsoft Sans Serif;}}
{\colortbl ;\red0\green0\blue0;}
\viewkind4\uc1\pard\ltrpar\sa200\sl276\slmult1\f0\fs16 What is a Reverse Phone Lookup?\par
Know who is calling your family with a Reverse Phone search. \par
Our report includes information, when available, associated with \par
Residential, business, unlisted, non-published.\par
\pard\ltrpar\cf1 Results\cf0 may include: Callers Name, address, email address, carrier, Caller\rquote s connection status and more.\f1\fs17\par
}
" Tip="{\rtf1\fbidis\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fswiss\fprq2\fcharset0 Verdana;}{\f1\fnil\fcharset0 Microsoft Sans Serif;}}
\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
\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
}
"/></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\fprq2\fcharset0 Verdana;}{\f1\fnil Arial Unicode MS;}}
{\*\generator Msftedit 5.41.21.2508;}\viewkind4\uc1\pard\sa200\sl276\slmult1\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\deflang1033{\fonttbl{\f0\fswiss\fprq2\fcharset0 Verdana;}{\f1\fnil\fcharset0 Microsoft Sans Serif;}}
{\colortbl ;\red0\green0\blue0;}
\viewkind4\uc1\pard\ltrpar\sa200\sl276\slmult1\f0\fs16 What is a Reverse Phone Lookup?\par
Know who is calling your family with a Reverse Phone search. \par
Our report includes information, when available, associated with \par
Residential, business, unlisted, non-published.\par
\pard\ltrpar\cf1 Results\cf0 may include: Callers Name, address, email address, carrier, Caller\rquote s connection status and more.\f1\fs17\par
}
" Tip="{\rtf1\fbidis\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fswiss\fprq2\fcharset0 Verdana;}{\f1\fnil\fcharset0 Microsoft Sans Serif;}}
\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
\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
}
" />
</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\fprq2\fcharset0 Verdana;}{\f1\fnil Arial Unicode MS;}}
{\*\generator Msftedit 5.41.21.2508;}\viewkind4\uc1\pard\sa200\sl276\slmult1\lang1033\f0\fs16 More Search Products! \line\line Email Search Employment Trace Reverse Phone/Cell Search \par
Web Site Ownership Social Net Search Sex Offender\par
Intl People/Business Search Email Lookup \par
\f1\par
}
" Tip="{\rtf1\ansi\deff0{\fonttbl{\f0\fswiss\fprq2\fcharset0 Verdana;}{\f1\fnil Arial Unicode MS;}}
{\*\generator Msftedit 5.41.21.2508;}\viewkind4\uc1\pard\sa200\sl276\slmult1\lang1033\f0\fs16 1. Enter you\rquote re subject\rquote s complete last and first name\par
2. Filter you\rquote re search by including the state and/or city fields to narrow down search results.\par
3. Use Proper/Formal names, limit you\rquote re use of Nick names \line\pard Example Robert for \ldblquote Bob\rdblquote Richard for \ldblquote ***\rdblquote\f1\par
}
" />
<dbo.SearchCategories SearchCategory="Address" DisplayOptionOrder="2" Comment="{\rtf1\fbidis\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fswiss\fprq2\fcharset0 Verdana;}{\f1\fnil\fcharset0 Microsoft Sans Serif;}}
\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
\pard\ltrpar\f1\fs17\par
}
" Tip="{\rtf1\fbidis\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fswiss\fprq2\fcharset0 Verdana;}{\f1\fnil\fcharset0 Microsoft Sans Serif;}}
\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
\pard\ltrpar\f1\fs17\par
}
" />
<dbo.SearchCategories SearchCategory="Phone" DisplayOptionOrder="3" Comment="{\rtf1\fbidis\ansi\ansicpg1252\deff0{\fonttbl{\f0\fswiss\fprq2\fcharset0 Verdana;}{\f1\fnil Arial Unicode MS;}}
\viewkind4\uc1\pard\ltrpar\sb100\sa100\sl276\slmult1\lang1033\f0\fs16 Who's Calling? Do you need to know who's on the other end of that phone number?\par
The report includes information, when available, associated with residential, business, cell, unlisted, non-published "Registered Owner(s)" including their Address, Possible Date of Birth. Options include, search for available public records for that listed individual\par
\pard\ltrpar\f1\par
}
" Tip="{\rtf1\fbidis\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fswiss\fprq2\fcharset0 Verdana;}{\f1\fnil\fcharset0 Microsoft Sans Serif;}}
\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
\pard\ltrpar\f1\fs17\par
}
" />
<dbo.SearchCategories SearchCategory="Email " DisplayOptionOrder="4" Comment="{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fswiss\fprq2\fcharset0 Verdana;}{\f1\fnil Arial Unicode MS;}}
{\*\generator Msftedit 5.41.21.2508;}\viewkind4\uc1\pard\sa200\sl276\slmult1\f0\fs16 Contact them immediately!! \line Results may Include: \par
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
\pard\f1\par
}
" Tip="{\rtf1\fbidis\ansi\ansicpg1252\deff0{\fonttbl{\f0\fswiss\fprq2\fcharset0 Verdana;}}
\viewkind4\uc1\pard\ltrpar\lang1033\f0\fs16 Enter \ldblquote Any\rdblquote email address associated with your subject. We can track older emails dating back to 1999.\par
}
" />
<dbo.SearchCategories SearchCategory="SSN " DisplayOptionOrder="5" Comment="{\rtf1\fbidis\ansi\ansicpg1252\deff0{\fonttbl{\f0\fswiss\fprq2\fcharset0 Verdana;}{\f1\fnil Arial Unicode MS;}}
\viewkind4\uc1\pard\ltrpar\sa200\sl276\slmult1\lang1033\f0\fs16 What is SSN People Search?\par
Locating People by their SSN is another method to search for family members, old friends and associates. Our People Search report includes addresses & phone numbers, relatives & associates, background options & more.\par
\pard\ltrpar\f1\par
}
" Tip="{\rtf1\fbidis\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fswiss\fprq2\fcharset0 Verdana;}{\f1\fnil Arial Unicode MS;}}
\viewkind4\uc1\pard\ltrpar\sa200\sl276\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
}
" />
</dbo.SearchOptions>
<dbo.SearchOptions SearchOption="Email Search" DisplayOptionOrder="2">
<dbo.SearchCategories SearchCategory="Email Search" DisplayOptionOrder="1" Comment="{\rtf1\fbidis\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fswiss\fprq2\fcharset0 Verdana;}{\f1\fnil\fcharset0 Microsoft Sans Serif;}}
\viewkind4\uc1\pard\ltrpar\f0\fs16 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
}
" Tip="{\rtf1\fbidis\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fswiss\fprq2\fcharset0 Verdana;}{\f1\fnil\fcharset0 Microsoft Sans Serif;}}
\viewkind4\uc1\pard\ltrpar\sa200\sl276\slmult1\f0\fs16 Enter you\rquote re subject\rquote s complete Name or Street Address. \par
\pard\ltrpar\f1\fs17\par
}
" />
<dbo.SearchCategories SearchCategory="Reverse Email" DisplayOptionOrder="2" Comment="{\rtf1\fbidis\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fswiss\fprq2\fcharset0 Verdana;}{\f1\fnil\fcharset0 Microsoft Sans Serif;}}
\viewkind4\uc1\pard\ltrpar\f0\fs16 With over 800 Million individual & Business email records, Human Finders is the Largest source for Email searches in the US, Canada, UK and Worldwide.\f1\fs17\par
}
" Tip="{\rtf1\fbidis\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fswiss\fprq2\fcharset0 Verdana;}{\f1\fnil\fcharset0 Microsoft Sans Serif;}}
\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
\pard\ltrpar Example Robert for \ldblquote Bob\rdblquote Richard for \ldblquote ***\rdblquote\f1\fs17\par
}
" />
</dbo.SearchOptions>
</dbo.ServiceOptions>
</services>

"Juan T. Llibre" <nomailreplies@xxxxxxxxxxx> wrote in message news:es4Opct7JHA.5780@xxxxxxxxxxxxxxxxxxxxxxx
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" <NoSpam@xxxxxxxxxxx> wrote in message news:eHVNods7JHA.728@xxxxxxxxxxxxxxxxxxxxxxx
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" <nomailreplies@xxxxxxxxxxx> wrote in message news:eSxZaf26JHA.728@xxxxxxxxxxxxxxxxxxxxxxx
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" <NoSpam@xxxxxxxxxxx> wrote in message news:%23qwivK26JHA.4632@xxxxxxxxxxxxxxxxxxxxxxx
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

References:
- Is there a way to convert ms sql 2008 tables to XML files?
  - From: Eric S
- Re: Is there a way to convert ms sql 2008 tables to XML files?
  - From: Juan T. Llibre
- Re: Is there a way to convert ms sql 2008 tables to XML files?
  - From: Eric S
- Re: Is there a way to convert ms sql 2008 tables to XML files?
  - From: Juan T. Llibre
- Re: Is there a way to convert ms sql 2008 tables to XML files?
  - From: Eric S

Prev by Date: Re: Is there a way to convert ms sql 2008 tables to XML files?
Next by Date: Re: Detailsview updading issue
Previous by thread: Re: Is there a way to convert ms sql 2008 tables to XML files?
Next by thread: Re: Is there a way to convert ms sql 2008 tables to XML files?
Index(es):
- Date
- Thread