Extracting XML data from SQL 2000 to SQL 2005 database table.

Hi,

We need to extract data from XML that is contained in TEXT columns in Sql Server 2000 database table. Can this be done easily with SSIS (NB we cannot use schemas easily with our data)? Please kindly provide us the solution in order to resolve this problem.

Thanks

VDeevi wrote:

Hi,

We need to extract data from XML that is contained in TEXT columns in Sql Server 2000 database table. Can this be done easily with SSIS (NB we cannot use schemas easily with our data)? Please kindly provide us the solution in order to resolve this problem.

Thanks

What methods have you tried so far and why do they fail?

-Jamie|||Hi Jamie,

Thanks for your reply, We tried with OPENXML, sp_xml_prepareddocument etc. but they are all round about things we want to know is there any simple technique in SSIS in order to retrieve the data from XML stored in SQL Server 2000 TEXT field.

Thanks,|||I've just had a go at this...

When you select the data a TEXT field appears in the SSIS pipeline as a DT_TEXT. To insert it into a XML field use a derived column transform to convert it to DT_NTEXT.
If you don't like DT_NTEXTs in your pipeline then convert it to DT_WSTR and then to DT_STR which can also be inserted into an XML field.

So yes, SSIS can do this very very well.

-Jamie

Extracting currency symbol and measurement units using tSQL

Hi guys,
Does anyone know how to extract the currency symbol and measurement
unitsusing an ISQL query?
For instance, in .NET I can very easily do the following
-Thread.CurrentThread.CurrentCulture.NumberFormat.CurrencySymbol and
System.Globalization.RegionInfo.CurrentRegion.IsMetric
Is such a thing possible under SQL Server?
Cheers
MBmukesh bhakta (mukesh_bhakta@.hotmail.com) writes:
> Does anyone know how to extract the currency symbol and measurement
> unitsusing an ISQL query?
>
> For instance, in .NET I can very easily do the following
> -Thread.CurrentThread.CurrentCulture.NumberFormat.CurrencySymbol and
> System.Globalization.RegionInfo.CurrentRegion.IsMetric
> Is such a thing possible under SQL Server?
I don't think it even makes sense. Measurement unit certanly doesn't,
because SQL Server does not use any measurement unit as far as I recall.
Currency symbols are used with the money data type, but I don't really
know whether this affected by language settings. (Which are separate from
Windows regional settings.) A quick test indicated that all of $,
and were understood as currency signs.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I have found the only way is to access the Windows registry using the
sp's supplied inside SQL Server.|||mukesh bhakta (mukesh_bhakta@.hotmail.com) writes:
> I have found the only way is to access the Windows registry using the
> sp's supplied inside SQL Server.
But I can't really see that you have any use for this information?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx