Copy data to another server via XML?

Hello...
I have to transfer/copy data from our SQL 2005 server to another server that
is NOT part of our domain. I was figuing to use XML to do this...but we hav
e
some image data types in some of our tables and I cannot figure out how to
include that data in the XML.
Does anyone know of a way to do this..or an article explaining it?
thanks for any help
- willHello dw,
If you are using SQL Server 2005, take a look at Integration Services.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Thanks for the advice. I just found out that the customer requirements do
not include a need for image/binary data...so I am now thinking of using the
HTTP Endpoints stuff...not really sure as I don't know too much about it
yet. But it looks promising.
"Kent Tegels" wrote:

> Hello dw,
> If you are using SQL Server 2005, take a look at Integration Services.
> Thanks!
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>|||dw wrote:
> Thanks for the advice. I just found out that the customer requirements do
> not include a need for image/binary data...so I am now thinking of using t
he
> HTTP Endpoints stuff...not really sure as I don't know too much about it
> yet. But it looks promising.
For the record, XML itself is designed for text information, so it
cannot hold binary (image) data direct: it has to be encoded into
text characters first or referenced externally (like HTML).
But as Kent has pointed out, suppliers provide built-in ways to do
this for you.
///Peter
--
XML FAQ: http://xml.silmaril.ie/
> "Kent Tegels" wrote:
>

Copy annotations out of xml/ dtsx package

Is there a way to strip out the annotations from a dtsx package. I'm looking at quite a few and would like to automaticly pull those out and store them somewhere in either a table or a text file.

Is this possible or am I going to have to hand copy them out?

Thanks for the help
Saitham8

The dtsx file is in xml format, so one would think that this would be possible with an XPath query. However, I made a copy of my dtsx file, then added several annotations and saved. The modified dtsx file showed a new last modified date, but when I used WinMerge and fc (even fc /b - for a binary comparison) there were no changes.

So then I created a brand new blank SSIS package. Then I saved it and made a copy of the entire solution directory structure. Then I added an annotation and saved and closed BIDS. Then I used WinMerge to compare the two directory structures. The only file that was different was the Integration Services Project3.suo file which is at the solution directory level.

To further test I opened up the unaltered copy deleted its blank package and then imported the annotated package. It came up without annotations!

Conclusion: the annotations are stored in the solution in the .suo file!

What kind of design flaw is this!

|||

If that were true then I wouldn't be able to copy a package containing an annotation into a seperate package and have the annotation show up. Which I can.

-Jamie

|||

Jamie,

I am as surprised as you. I am just reporting the result of some testing I did. I made an annotation in a package, saved it and imported it into another solution and the anotation did not come with it. I am running SQL 2005 no sp. I have not yet tried it with SP 1.

Are you having success doing this on SP 1 or pre SP 1?

|||

David Lundell wrote:

Jamie,

I am as surprised as you. I am just reporting the result of some testing I did. I made an annotation in a package, saved it and imported it into another solution and the anotation did not come with it. I am running SQL 2005 no sp. I have not yet tried it with SP 1.

Are you having success doing this on SP 1 or pre SP 1?

AHA. I am on SP1. Perhaps that's why.

-Jamie

|||So it sounds like I'll need to move up to sp1 and then take a look for the anotation tag in the xml. I won't have a chance to get to this for a little while but I'll let you know what I find.|||

I upgraded to SP 1 and saw the same behavior as before.

I create a blank IS project

I copy the project to a new directory.

I annotate the otherwise blank package.dtsx file

I hit the save all button.

I then use winmerge to compare the two directory structures. Again the only file I see that has changed is .suo

So I open the untouched copy. I delete its package.dtsx. I then import existing package and grab it from the original project. I don't see the annotation.

Jamie, could you provide us a step by step of how you get the annotations to move with the package?

|||Everytime you save a modified package in designer, at least two properties are changed: VersionGuid (new GUID is generated) and VersionBuild (build is incremented), so the dtsx files should differ.
I would check if you've saved the modified package.
Once you added an annotation, click away from the annotation on some other place in control flow. The title of the document should get asterisk at the end (like "Package 1.dtsx [Design]*", meaning it is modified and will be saved. Now click save all.
Once the modified file has been saved, windiff should show quite a bit of differences in DTSX files. The annotations and diagram layouts are stored in DTSX file, not in SUO files. This is true both for RTM and SP1 builds.|||

User error. The annotations are stored in the package, not in the SUO file.

You can't use XSLT to strip those out unless you can convert the binary encodings which is how they're persisted.

K

|||

Michael,

I have tried what you suggested. I added the annotation and then clicked in some other place in the control flow. The title of the document did not get an asterisk. I did click Save, and I also tried Save All. I still got the same result.

Now to report the real bug! I closed the package and then when I reopened it the annotation was gone. Since all of these were throwaway packages I was creating just to research the issue for the original posting I did not have a need to go back into them. I did so this morning, and the annotation did not stick in any of them.

So I modified my experiment. I added something else to the control flow, an ActiveX Script task. Then I saved it and copied the whole solution directory. Then I added the annotation (no asterisk appeared). Then I renamed the ActiveX Script Task. At that point I got the asterisk to appear. Then in the comparison I see a difference in the package.dtsx. It looks to me like the text of the annotation is stored in the binary attribute of the ddsxmlobjectstreaminitwrapper element. Then when I move the package the annotation comes with it.

So the bug is if the only change I make is adding or modifying an annotation then BIDS does not indicate that I have changed the package and hence does not save the new or modified annotation! Please try it and you will find the bug. In summary if I go into a package for the sole purpose of adding and/or modifying annotations they will not get saved.

I am glad however to discover that I was wrong about the .suo file, because that sure did not make sense! But based on the actual bug reported and my exact steps you can see how it looked like that!

Thanks for the help, and please report back on what you find out with this bug.

|||David I was noticing that the annotations disapear if you don't have any text in them. I still havent gotten a chance to try my hand at this stuff. Thanks a ton for looking into it.|||

Kirk,

You are correct. The annotations are stored in the package.

However, as I documented in my reply to Michael Entin - MSFT ,
there is a bug in BIDS that caused the behavior I described.

Essentially, if all you do is add or modify annotations those changes do not get saved at all. That is why I only saw changes in the .suo

Converting XML Datetime to SQL Datetime

I have an XML colmn in SQL 2005 table which looks like:

<abc>

<abcdate>2007-01-31T13:47:27.25-05:00</abcdate>

</abc>

The following query :

SELECT xmlColumn.value('(/abc/abcDate)[1]', 'nvarchar(30)') FROM abcTABLE

Returns 2007-01-31T13:47:27.25-05:00

SELECT CAST(xmlColumn.value('(/abc/abcDate)[1]', 'nvarchar(30)') AS DATETIME) FROM abcTABLE

Returns

Msg 241, Level 16, State 1, Line 1

Conversion failed when converting datetime from character string.

--

SELECT CAST(xmlColumn.value('(/abc/abcDate)[1]', 'nvarchar(19)') AS DATETIME) FROM abcTABLE

Returns 2007-01-31 13:47:27.000

because length of 19 trims the milliseconds

Is it possible to convert this type of XML data type and still acheive accuracy to the milliseconds?

Thanks

Gary

You could use convert function with 126/127 style (for more info http://msdn2.microsoft.com/en-us/library/ms187928.aspx):

declare @.t1 varchar(25)

declare @.t2 varchar(25)

set @.t1 = '2007-01-31T13:47:27.25-05:00'

set @.t2 = '2006-12-12T23:45:12.10'

select convert(datetime,@.t2,126)

select convert(datetime,@.t1,127) --It must works, but don't work on my PC. I think something wrong with my system

|||

Hi Konstantin Kosinsky

It is not working at my machine as well, the "-" is the culprit still.

Style 127 is for ISO8601 with time zone Z: yyyy-mm-ddThh:mm:ss.mmmZ

It does not like the hyphen "-"

|||

Just quote from BOL:

The optional time zone indicator, Z, is used to make it easier to map XML datetime values that have time zone information to SQL Server datetime values that have no time zone. Z is the indicator for time zone UTC-0. Other time zones are indicated with HH:MM offset in the + or - direction. For example: 2006-12-12T23:45:12-08:00.

But it does not work. :).

At this moment i could propose substring by last "-" and use 126 or 127 style

|||

The docs are misleading here. If you look carefully at the table above the quote, style 127 only work with Zulu timezone ("Z"). The workaround here is to use XQuery simple type construction.

declare @.t1 xml

set @.t1 = '<abc>2007-01-31T13:47:27.25+05:00</abc>'

select @.t1.value('xs:dateTime(/abc[1])', 'datetime')

-galex

|||

Hi Galex

I tried:

SELECT

Assessmentxml.value('xs:dateTime(/Abc/AbcDate)[1]', 'datetime') AS [AssessmentExpectedStartDate]

FROM dbo.Assessment

I got:

Msg 2365, Level 16, State 1, Line 10

XQuery [dbo.Assessment.AssessmentXML.value()]: Cannot explicitly convert from 'xdt:untypedAtomic *' to 'xs:dateTime'

Then I tried:

SELECT

Assessmentxml.value('(/Abc/AbcDate)[1] CAST AS xs:dateTime', 'datetime') AS [AssessmentExpectedStartDate]

FROM dbo.Assessment

and got

Msg 2370, Level 16, State 1, Line 2

XQuery [dbo.Assessment.AssessmentXML.value()]: No more tokens expected at the end of the XQuery expression. Found 'CAST'.

What is the correct syntax to make it work?

Thanks

|||

In SQL Server, the expression you are casting (construction is the same) requires a singleton. Since you are using untyped XML, you need to use a positional predicate.

Please try:

SELECT

Assessmentxml.value('xs:dateTime(/Abc/AbcDate[1])', 'datetime') AS [AssessmentExpectedStartDate]

FROM dbo.Assessment

Notice the [1] predicate is inside the construction.

Also, XQuery is case sensitive, so you should be using "cast as" instead of "CAST AS".

-galex

|||

Hi Galex

I get the same error. Please try this:

set ansi_nulls, quoted_identifier, ansi_warnings, ansi_padding ON

declare @.Ax table

( AxID uniqueidentifier not null default(newid())

, AxRefDateTag sysname

, AxXML as cast(

'<Assessment xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<AssessmentID>' + cast(AxID as sysname) + '</AssessmentID>'

+ AxRefDateTag + '</Assessment>' as xml)

)

insert @.Ax(AxRefDateTag) values('<AssessmentReferenceDate>2007-01-31T13:47:27.25+05:00</AssessmentReferenceDate>')

SELECT

AxRefDateTag

,AxXml.value('xs:dateTime(/Assessment/AssessmentReferenceDate[1])', 'datetime') AS [AssessmentExpectedStartDate]

FROM @.Ax

|||

Gary,

That was my bad. I forgot to put keep the () around the path expression.

xs:dateTime((/Assessment/AssessmentReferenceDate)[1])

Keep in mind, this is also another option, but has different semantics:

xs:dateTime(/Assessment[1]/AssessmentReferenceDate[1])

Sorry for the confusion.

Regards,

Galex

|||This works well :) - thanks a lot Galex|||

Hi Gary

I am also getting the same issue.

Actually straightforward I cannot use the one which you sent ( 'xsateTime(/Assessment/AssessmentReferenceDate[1])', 'datetime')

I cannot say [1], i have to use the variable instead of that, because I am looping through the data.

I am using the below code. It is working fine when the date time is in this format 2007-01-31T13:47:27.25' only.

CAST(CAST(Message.query('data(//SHIPMENT/ISSUE_DT)[sql:variable("@.vcounter")]') AS VARCHAR) AS DATETIME) END,

I tried like this with the format you had given

Message.value('xsateTime((//SHIPMENT/ISSUE_DT)[sql:variable("@.vcounter")])', 'datetime'))

but i am getting an error while compiling

XQuery [Ediinbound.Message.value()]: Cannot explicitly convert from 'xdt:untypedAtomic *' to 'xsateTime'.

Kindly help me ASAP...

Thanks

Ram

|||

This is how it worked. Galex showed me how to put brackets around the XPATH.

set ansi_nulls, quoted_identifier, ansi_warnings, ansi_padding ON

declare @.Ax table

( AxID uniqueidentifier not null default(newid())

, AxRefDateTag sysname

, AxXML as cast(

'<Assessment xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<AssessmentID>' + cast(AxID as sysname) + '</AssessmentID>'

+ AxRefDateTag + '</Assessment>' as xml)

)

insert @.Ax(AxRefDateTag) values('<AssessmentReferenceDate>2007-01-31T13:47:27.25+05:00</AssessmentReferenceDate>')

SELECT * FROM @.Ax

SELECT

AxRefDateTag

,AxXml.value('xsateTime((/Assessment/AssessmentReferenceDate)[1])', 'datetime') AS [AssessmentExpectedStartDate]

FROM @.Ax

I haven't tried the variable before, but I think from the above explanation [1] or singleton is a MUST in SQL Server queries.

Converting XML Datetime to SQL Datetime

I have an XML colmn in SQL 2005 table which looks like:

<abc>

<abcdate>2007-01-31T13:47:27.25-05:00</abcdate>

</abc>

The following query :

SELECT xmlColumn.value('(/abc/abcDate)[1]', 'nvarchar(30)') FROM abcTABLE

Returns 2007-01-31T13:47:27.25-05:00

SELECT CAST(xmlColumn.value('(/abc/abcDate)[1]', 'nvarchar(30)') AS DATETIME) FROM abcTABLE

Returns

Msg 241, Level 16, State 1, Line 1

Conversion failed when converting datetime from character string.

--

SELECT CAST(xmlColumn.value('(/abc/abcDate)[1]', 'nvarchar(19)') AS DATETIME) FROM abcTABLE

Returns 2007-01-31 13:47:27.000

because length of 19 trims the milliseconds

Is it possible to convert this type of XML data type and still acheive accuracy to the milliseconds?

Thanks

Gary

You could use convert function with 126/127 style (for more info http://msdn2.microsoft.com/en-us/library/ms187928.aspx):

declare @.t1 varchar(25)

declare @.t2 varchar(25)

set @.t1 = '2007-01-31T13:47:27.25-05:00'

set @.t2 = '2006-12-12T23:45:12.10'

select convert(datetime,@.t2,126)

select convert(datetime,@.t1,127) --It must works, but don't work on my PC. I think something wrong with my system

|||

Hi Konstantin Kosinsky

It is not working at my machine as well, the "-" is the culprit still.

Style 127 is for ISO8601 with time zone Z: yyyy-mm-ddThh:mm:ss.mmmZ

It does not like the hyphen "-"

|||

Just quote from BOL:

The optional time zone indicator, Z, is used to make it easier to map XML datetime values that have time zone information to SQL Server datetime values that have no time zone. Z is the indicator for time zone UTC-0. Other time zones are indicated with HH:MM offset in the + or - direction. For example: 2006-12-12T23:45:12-08:00.

But it does not work. :).

At this moment i could propose substring by last "-" and use 126 or 127 style

|||

The docs are misleading here. If you look carefully at the table above the quote, style 127 only work with Zulu timezone ("Z"). The workaround here is to use XQuery simple type construction.

declare @.t1 xml

set @.t1 = '<abc>2007-01-31T13:47:27.25+05:00</abc>'

select @.t1.value('xs:dateTime(/abc[1])', 'datetime')

-galex

|||

Hi Galex

I tried:

SELECT

Assessmentxml.value('xs:dateTime(/Abc/AbcDate)[1]', 'datetime') AS [AssessmentExpectedStartDate]

FROM dbo.Assessment

I got:

Msg 2365, Level 16, State 1, Line 10

XQuery [dbo.Assessment.AssessmentXML.value()]: Cannot explicitly convert from 'xdt:untypedAtomic *' to 'xs:dateTime'

Then I tried:

SELECT

Assessmentxml.value('(/Abc/AbcDate)[1] CAST AS xs:dateTime', 'datetime') AS [AssessmentExpectedStartDate]

FROM dbo.Assessment

and got

Msg 2370, Level 16, State 1, Line 2

XQuery [dbo.Assessment.AssessmentXML.value()]: No more tokens expected at the end of the XQuery expression. Found 'CAST'.

What is the correct syntax to make it work?

Thanks

|||

In SQL Server, the expression you are casting (construction is the same) requires a singleton. Since you are using untyped XML, you need to use a positional predicate.

Please try:

SELECT

Assessmentxml.value('xs:dateTime(/Abc/AbcDate[1])', 'datetime') AS [AssessmentExpectedStartDate]

FROM dbo.Assessment

Notice the [1] predicate is inside the construction.

Also, XQuery is case sensitive, so you should be using "cast as" instead of "CAST AS".

-galex

|||

Hi Galex

I get the same error. Please try this:

set ansi_nulls, quoted_identifier, ansi_warnings, ansi_padding ON

declare @.Ax table

( AxID uniqueidentifier not null default(newid())

, AxRefDateTag sysname

, AxXML as cast(

'<Assessment xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<AssessmentID>' + cast(AxID as sysname) + '</AssessmentID>'

+ AxRefDateTag + '</Assessment>' as xml)

)

insert @.Ax(AxRefDateTag) values('<AssessmentReferenceDate>2007-01-31T13:47:27.25+05:00</AssessmentReferenceDate>')

SELECT

AxRefDateTag

,AxXml.value('xs:dateTime(/Assessment/AssessmentReferenceDate[1])', 'datetime') AS [AssessmentExpectedStartDate]

FROM @.Ax

|||

Gary,

That was my bad. I forgot to put keep the () around the path expression.

xs:dateTime((/Assessment/AssessmentReferenceDate)[1])

Keep in mind, this is also another option, but has different semantics:

xs:dateTime(/Assessment[1]/AssessmentReferenceDate[1])

Sorry for the confusion.

Regards,

Galex

|||This works well :) - thanks a lot Galex|||

Hi Gary

I am also getting the same issue.

Actually straightforward I cannot use the one which you sent ( 'xsateTime(/Assessment/AssessmentReferenceDate[1])', 'datetime')

I cannot say [1], i have to use the variable instead of that, because I am looping through the data.

I am using the below code. It is working fine when the date time is in this format 2007-01-31T13:47:27.25' only.

CAST(CAST(Message.query('data(//SHIPMENT/ISSUE_DT)[sql:variable("@.vcounter")]') AS VARCHAR) AS DATETIME) END,

I tried like this with the format you had given

Message.value('xsateTime((//SHIPMENT/ISSUE_DT)[sql:variable("@.vcounter")])', 'datetime'))

but i am getting an error while compiling

XQuery [Ediinbound.Message.value()]: Cannot explicitly convert from 'xdt:untypedAtomic *' to 'xsateTime'.

Kindly help me ASAP...

Thanks

Ram

|||

This is how it worked. Galex showed me how to put brackets around the XPATH.

set ansi_nulls, quoted_identifier, ansi_warnings, ansi_padding ON

declare @.Ax table

( AxID uniqueidentifier not null default(newid())

, AxRefDateTag sysname

, AxXML as cast(

'<Assessment xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<AssessmentID>' + cast(AxID as sysname) + '</AssessmentID>'

+ AxRefDateTag + '</Assessment>' as xml)

)

insert @.Ax(AxRefDateTag) values('<AssessmentReferenceDate>2007-01-31T13:47:27.25+05:00</AssessmentReferenceDate>')

SELECT * FROM @.Ax

SELECT

AxRefDateTag

,AxXml.value('xsateTime((/Assessment/AssessmentReferenceDate)[1])', 'datetime') AS [AssessmentExpectedStartDate]

FROM @.Ax

I haven't tried the variable before, but I think from the above explanation [1] or singleton is a MUST in SQL Server queries.

Converting SQL Server Data into XML

Hi Folks!

I have the following sql script I wrote using Explicit Option to convert data from SQL Server tables into a single XML file. I am aware of the tedious nature of the select statements, but this seems to the only option I have to depict parent-child nature of the data in XML format and also to schedule it as a job to run via SQL Server Agent.

My problem is that as I run this using the following command, I get "There is insufficient system memory to run this query" error.
I am using the following commnd:
exec master..xp_cmdshell 'bcp "EXEC swr_cv2..sproc_BuildXMLTree" queryout "C:\test.xml" -U -P -c -r -t'

Is there any way I can tune my query to fix that error?

Thanks so much for your help!

-ParulHi Folks!

I am aware of the tedious nature of the select statements,
-Parul

Really tedious Parul,For the first time I am seeing so big a proc... rather a Mamoth ;)|||I know, that's because the XML feed layout is huge as well...is there a way to tune a FOR XML EXPLICIT query?|||I know, that's because the XML feed layout is huge as well...is there a way to tune a FOR XML EXPLICIT query?
You can increase the max server memory,but I am not sure whether that will help you...check this (http://sqljunkies.com/PrintContent.aspx?type=tutorial&id=0D4FF40A-695C-4327-A41B-F9F2FE2D58F6)|||Thanks! I am really hoping not to tamper with the server settings as it is a production database. I am hoping I can may be build a temp table around this data, any ideas?|||Thanks! I am really hoping not to tamper with the server settings as it is a production database. I am hoping I can may be build a temp table around this data, any ideas?
have you tried XPath queries in this case ?|||No, I am not sure if I can write XPath Queries and schedule them via SQL Server Agent. Do you know if that is possible? Or do I need a .net application for it?|||No, I am not sure if I can write XPath Queries and schedule them via SQL Server Agent. Do you know if that is possible? Or do I need a .net application for it?
check this (http://www.aspfree.com/c/a/MS-SQL-Server/XML-and-the-SQL-2000-Server-part-3-XML-Data-with-XPath-Queries/1/) ...|||Thanks! This seems helpful in generating an XML layout; however, I am still wondering how I can schedule this to run daily...|||Thanks! This seems helpful in generating an XML layout; however, I am still wondering how I can schedule this to run daily...
An easy option can be Altova xmlspy 2006.
Try this www.altova.com (http://www.altova.com) and download XMLSPY 2006|||Thanks! BTW, I got my sql query to work! I replaced all the unions with inserts - now I am inserting all the data into an actual physical data, and then running FOR XML EXPLICIT on it. It seems to be working fine now!

I had a question though - do you know if there is a sql command to merge two files. Basically, I want to merge the header for the xml file with actual xml data in another file - is that possible without running any DOS commands?

Thanks so much!

converting sql server 2005 data into xml format

How to convert table in database, in microsoft sql server 2005 into xml format?If you are trying to get data from SQL in XML format, you can query the table and can use "FOR XML" to get data in XML format.|||

Hello,

You can use XPath queries over annotated XSD schemas(http://msdn2.microsoft.com/en-us/library/ms171802.aspx) to get the data in xml format.

I hope this helps,

Monica Frintu

converting sql server 2005 data into xml format

How to convert table in database, in microsoft sql server 2005 into xml format?If you are trying to get data from SQL in XML format, you can query the table and can use "FOR XML" to get data in XML format.|||

Hello,

You can use XPath queries over annotated XSD schemas(http://msdn2.microsoft.com/en-us/library/ms171802.aspx) to get the data in xml format.

I hope this helps,

Monica Frintu