Extracting SQL fields from XML Data stored in NText field

I have a interesting situation, where XML is stored inside of SQL NText
field.
Unfortunately, I need to extract that data as SQL from the XML.
Here is the data in the BillingAddress field:
<?xml version="1.0" encoding="utf-16"?>
<Address>
<ID>-1</ID>
<FirstName>Joe</FirstName>
<LastName>McLean</LastName>
<StreeLine1>360 E Columbia 48</StreeLine1>
<City>Emerson</City>
<StateName>Arkansas</StateName>
<StateCode>4</StateCode>
<PostalCode>71740</PostalCode>
<CountryName>United States</CountryName>
<CountryCode>en-US</CountryCode>
<PhoneNumber>870-547-2251</PhoneNumber>
</Address>
I need to be able to have a SQL query that returns all of those nodes
(FirstName, LastName, etc) as individual SQL fields.
Can this be done?
Thanks in advance!
Robert Johnson
Internet Web ZoneHave you looked in to using OPENXML? SQL Server Books Online has tons of
information and examples on this. Also, check out www.sqlxml.org
Anith|||I spent hours looking. That is why I posted the question. If you don't have
an answer, please don't respond.
Robert
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23DNdh8yAFHA.3376@.TK2MSFTNGP12.phx.gbl...
> Have you looked in to using OPENXML? SQL Server Books Online has tons of
> information and examples on this. Also, check out www.sqlxml.org
> --
> Anith
>|||Here is an example:
CREATE TABLE t (
keycol INT NOT NULL PRIMARY KEY,
xmlval NTEXT )
INSERT t SELECT 1,
'<Address>
<ID>-1</ID>
<FirstName>Joe</FirstName>
<LastName>McLean</LastName>
<StreeLine1>360 E Columbia 48</StreeLine1>
<City>Emerson</City>
<StateName>Arkansas</StateName>
<StateCode>4</StateCode>
<PostalCode>71740</PostalCode>
<CountryName>United States</CountryName>
<CountryCode>en-US</CountryCode>
<PhoneNumber>870-547-2251</PhoneNumber>
</Address>'
DECLARE @.xml VARCHAR(8000), @.idoc INT
SET @.xml = ( SELECT CAST( xmlval AS VARCHAR(8000 ) )
FROM t WHERE keycol = 1 )
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.xml
SELECT *
FROM OPENXML ( @.idoc, '/Address', 2 )
WITH ( FirstName VARCHAR(20),
LastName VARCHAR(20) )
EXEC sp_xml_removedocument @.idoc
Anith|||There is no need to be rude. Where does it say in your original post that
you " ... spent hours looking ..."? And specifically, where does it say that
you spent hours looking at the topics in BOL about OpenXML? Or browsing
through the excellent resource at sqlxml.org?
All we know is what we see in your post. Don't expect us to be able to
somehow be aware of all the research that you've already done before posting
your question.
Ah, I see that Anith was gracious enough to overlook your rudeness and post
an example. Good for Anith.
Bob Barrows
Robert Johnson wrote:
> I spent hours looking. That is why I posted the question. If you
> don't have an answer, please don't respond.
> Robert
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:%23DNdh8yAFHA.3376@.TK2MSFTNGP12.phx.gbl...
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Extracting RDL file from published reports in RS 2000

In order to have a backup of the rdl files for reporting, does exist a manner
to extract the rdl files inside RS starting from published reports?
In other terms, is it possible to revert the report publishing process in
order to have the rdl files?
Many thanksOn Sep 11, 3:44 am, Pasquale <Pasqu...@.discussions.microsoft.com>
wrote:
> In order to have a backup of the rdl files for reporting, does exist a manner
> to extract the rdl files inside RS starting from published reports?
> In other terms, is it possible to revert the report publishing process in
> order to have the rdl files?
> Many thanks
Try this application, works great for me. http://sqldbatips.com/showarticle.asp?ID=62|||Oh, well! It's very useful.
Many thanks
"Tim Toennies" wrote:
> On Sep 11, 3:44 am, Pasquale <Pasqu...@.discussions.microsoft.com>
> wrote:
> > In order to have a backup of the rdl files for reporting, does exist a manner
> > to extract the rdl files inside RS starting from published reports?
> > In other terms, is it possible to revert the report publishing process in
> > order to have the rdl files?
> > Many thanks
> Try this application, works great for me. http://sqldbatips.com/showarticle.asp?ID=62
>

Extracting info regarding ETL's

Dear all,
I'm pursuing for a method/query or any kind of task being able to find all
those DTS which own Oracle connections inside. It's very hard open one and
one (500)!!
Does anyone ever experienced or face up such requirement?
Thanks for your time and any input will be welcomed,
EnricI don't think it's possible to display dependencies of a DTS Package as it's
application code rather than SQL objects.
You may be able to save the DTS Packages as a Visual Basic file and then
search within the file. I have never tried this though.
HTH. Ryan
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:D0217DB6-3099-4664-ABE5-BE383B825C60@.microsoft.com...
> Dear all,
> I'm pursuing for a method/query or any kind of task being able to find all
> those DTS which own Oracle connections inside. It's very hard open one and
> one (500)!!
> Does anyone ever experienced or face up such requirement?
> Thanks for your time and any input will be welcomed,
> Enric|||hi again,
Well, in a fact it's possible instancing dtspkg.dll with VB
and navigate through a specific DTS and obtaining the value for the global
variables and so on... I am trying to avoid this way, it's useless.
Thanks anyway,
"Ryan" wrote:

> I don't think it's possible to display dependencies of a DTS Package as it
's
> application code rather than SQL objects.
> You may be able to save the DTS Packages as a Visual Basic file and then
> search within the file. I have never tried this though.
> --
> HTH. Ryan
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:D0217DB6-3099-4664-ABE5-BE383B825C60@.microsoft.com...
>
>|||I hope that the next version avoid this kind of situations.
"Enric" wrote:
> hi again,
> Well, in a fact it's possible instancing dtspkg.dll with VB
> and navigate through a specific DTS and obtaining the value for the global
> variables and so on... I am trying to avoid this way, it's useless.
> Thanks anyway,
> "Ryan" wrote:
>