How redirect SQL Sserver XML output?

I have to generate an XMl from a SQL Server 2000 table. Using the clause FOR
XML i can only send the result to screen or to file.
I want, instead, store the result, into a field column in another table.
There's a way i can do it without passing through a file (and OpenXML), but
storing the result directly into the field column?
tnks in advance
andrea
Do you want to say something along the line of
insert into T(xmlcol) values (select ... for xml ...)
?
If so, you will need to wait for SQL Server 2005. In SQL Server 2000, you
had to do some really ugly workarounds by moving the XML to the client and
then reload it back into the server.
Best regards
Michael
"vikys" <vikys@.hotmail.com> wrote in message
news:utcDmwwcFHA.3712@.TK2MSFTNGP09.phx.gbl...
>I have to generate an XMl from a SQL Server 2000 table. Using the clause
>FOR
> XML i can only send the result to screen or to file.
> I want, instead, store the result, into a field column in another table.
> There's a way i can do it without passing through a file (and OpenXML),
> but
> storing the result directly into the field column?
> tnks in advance
> andrea
>

how organize a junction table?

Hi
I am importing XML documents with XML bulk load.
The tables look like this (kind of) with a junction table C connecting tableA and tableB.

tableA(nameID integer PRIMARY Key IDENTITY, name string)

tableB(placeID integer PRIMARY Key IDENTITY , placename string)

tableC(placeID integer FOREIGN Key, nameID integer FOREIGN key)

NameID and placeID have the property identity, and the xsd is mapped so that they are inserted in tableC as well as their parent table when they are generated.

My problem now is, how do I make sure that each placename or name only occur once with one ID? When trying to insert a placename that already exist in the database, I dont want to generate a new placeID for it. Instead, I would like to fetch the existing placeID and inserting it with the new nameID in tableC. It should be a quite common problem, since most many to many relationships would result in a junction table. As mentioned, I am loading the data with XML bulk load, I dont know if that would complicate matter..
All help will be greatly appreciated!
regards, Helenayou're right, it is a common problem

when using a surrogate key, such as an identity column, remember that each table must actually have a "real" primary key (called a candidate key in data modelling terminology)

all you have to do is declare a unique constraint on the real key, and the database will then prvent you from entering duplicates

rudy|||Thanks for your reply.

The Identity columns in tableA and tableB are also the "real" primary key. The "real" key in tableC would be a composite key made up by tableA and tableB's primary keys. Name in tableA and placename in tableB are declared unique. The composite key in tableC should also be unique, then the database will prevent duplicates values. But when trying to insert a new name but an old placename for example, how do I get the new nameID but the old placeID into tableC? It feels like I need to do a search to see if the placename exists, if no just insert data, if yes fetch placeID for that particular placename and insert. And this would be necessary whenever a junction table is involved.

/Helena|||perhaps you could do some research on the difference between surrogate and natural keys, but i assure you, even if you declare an identity column as a primary key, so that as far as the database is concerned it is the primary key, it isn't the "real" primary key, not from a modelling perspective, not in regard to duplicates

yes, make the composite key in tableC its primary key

as for what to do with potential duplicates, and how to find the original entry, so duplcates are not entered, and so that the tableC row links the correct parent rows, i'm sorry, i cannot help you, i've never written an XSD application

perhaps you could eliminate the dupes from the data before you attempt to load it with XML

rudy|||Ok, I see your point..

Eliminating the duplicates is unfortunately not an option.

Anyway, thanks for your help!sql

how much storage does a null xml data type column use if...

1) it is in the same filegroup as the rest of the table?

2) it is stored in a filegroup separately from the rest of the table (I think this is allowed)?

moving this over to the database engine forum.sql

How long we can trust on FOR XML AUTO or FOR XML RAW

Hi,

I have been there a situation of an apprehension that Microsoft may issue some patch or hotfix in future for SQL SERVER, that will change the shape of XML results yielded by FOR XML AUTO or FOR XML RAW query.

Our query is going to be rigid in the application and the data would then be passed through sensitive application that may crash if xml is not valid, we have many types of xmls so we cannot create schema for each and every guy and same with EXPLICIT.

Is this superstition valid that I shouldnt trust Microsoft here ?

Any input in this will sincerely be appreciated.

Fahad

Hi,
Am not so soure about the patch but what exactly are you trying to achieve? maybe if you specified the problem we could try and work around the problem and find a solution.

|||We are using a query with FOR XML AUTO, but our apprehension is weather SQL Server GUYS will issue some service pack in future that will change the XML Format, we wont have access to change the logic or xsl in future because software is burnt into the hardware.|||I'd say the fact that they didn't change it with the release of SQL Server 2005 means they most likely won't.
But thats just my point of view.
|||

Fahad,

We put a lot of effort into keeping backwards compatiablity. If we do have breaking changes going forward, you can always put your database in back compat mode.

-galex

|||They DID change it in SQL Server 2005!
I tried to use 2005 for an application that exclusively uses FOR XML and not even the login worked, as the structure of the XML returned differed from 2000.

How long we can trust on FOR XML AUTO or FOR XML RAW

Hi,

I have been there a situation of an apprehension that Microsoft may issue some patch or hotfix in future for SQL SERVER, that will change the shape of XML results yielded by FOR XML AUTO or FOR XML RAW query.

Our query is going to be rigid in the application and the data would then be passed through sensitive application that may crash if xml is not valid, we have many types of xmls so we cannot create schema for each and every guy and same with EXPLICIT.

Is this superstition valid that I shouldnt trust Microsoft here ?

Any input in this will sincerely be appreciated.

Fahad

Hi,
Am not so soure about the patch but what exactly are you trying to achieve? maybe if you specified the problem we could try and work around the problem and find a solution.

|||We are using a query with FOR XML AUTO, but our apprehension is weather SQL Server GUYS will issue some service pack in future that will change the XML Format, we wont have access to change the logic or xsl in future because software is burnt into the hardware.|||I'd say the fact that they didn't change it with the release of SQL Server 2005 means they most likely won't.
But thats just my point of view.
|||

Fahad,

We put a lot of effort into keeping backwards compatiablity. If we do have breaking changes going forward, you can always put your database in back compat mode.

-galex

|||They DID change it in SQL Server 2005!
I tried to use 2005 for an application that exclusively uses FOR XML and not even the login worked, as the structure of the XML returned differed from 2000.

How long we can trust on FOR XML AUTO or FOR XML RAW

Hi,

I have been there a situation of an apprehension that Microsoft may issue some patch or hotfix in future for SQL SERVER, that will change the shape of XML results yielded by FOR XML AUTO or FOR XML RAW query.

Our query is going to be rigid in the application and the data would then be passed through sensitive application that may crash if xml is not valid, we have many types of xmls so we cannot create schema for each and every guy and same with EXPLICIT.

Is this superstition valid that I shouldnt trust Microsoft here ?

Any input in this will sincerely be appreciated.

Fahad

Hi,
Am not so soure about the patch but what exactly are you trying to achieve? maybe if you specified the problem we could try and work around the problem and find a solution.

|||We are using a query with FOR XML AUTO, but our apprehension is weather SQL Server GUYS will issue some service pack in future that will change the XML Format, we wont have access to change the logic or xsl in future because software is burnt into the hardware.|||I'd say the fact that they didn't change it with the release of SQL Server 2005 means they most likely won't.
But thats just my point of view.
|||

Fahad,

We put a lot of effort into keeping backwards compatiablity. If we do have breaking changes going forward, you can always put your database in back compat mode.

-galex

|||They DID change it in SQL Server 2005!
I tried to use 2005 for an application that exclusively uses FOR XML and not even the login worked, as the structure of the XML returned differed from 2000.

How long we can trust on FOR XML AUTO or FOR XML RAW

Hi,

I have been there a situation of an apprehension that Microsoft may issue some patch or hotfix in future for SQL SERVER, that will change the shape of XML results yielded by FOR XML AUTO or FOR XML RAW query.

Our query is going to be rigid in the application and the data would then be passed through sensitive application that may crash if xml is not valid, we have many types of xmls so we cannot create schema for each and every guy and same with EXPLICIT.

Is this superstition valid that I shouldnt trust Microsoft here ?

Any input in this will sincerely be appreciated.

Fahad

Hi,
Am not so soure about the patch but what exactly are you trying to achieve? maybe if you specified the problem we could try and work around the problem and find a solution.

|||We are using a query with FOR XML AUTO, but our apprehension is weather SQL Server GUYS will issue some service pack in future that will change the XML Format, we wont have access to change the logic or xsl in future because software is burnt into the hardware.|||I'd say the fact that they didn't change it with the release of SQL Server 2005 means they most likely won't.
But thats just my point of view.
|||

Fahad,

We put a lot of effort into keeping backwards compatiablity. If we do have breaking changes going forward, you can always put your database in back compat mode.

-galex

|||They DID change it in SQL Server 2005!
I tried to use 2005 for an application that exclusively uses FOR XML and not even the login worked, as the structure of the XML returned differed from 2000.

How Large Can XML Data in a Variable be for the XML Source?

We can pass XML to the XML Source in a variable, but I haven't seen anywhere how much data can be passed this way? Is there a limit beyond the limits of system memory?

Also, what data types are valid for the variable? Just String?

Hi John,
did you find any answer to this? I am very curious to know these limitations as well..

/Erik B
|||

Hi John,

I don't see any set size limitations in our code. Are you seeing any weird behavior when using large strings?

Yes, the source variable has to be of type string.

Thanks,

~Matt

|||

No, no wierd behavior yet. I just needed the information to help me decide which way to implement a certain piece of functionality.

So, I'll just assume I can create 2Gb worth of XML.

How insert more then 4000 characters?

I need insert many dates from tables to XML file, I use T-SQL, everything is OK, but in one step I can insert maximally 4000 character in uni-code.

Is there another way? Thanks' a lot.

I use such sequence T-SQL:

Declare

@.doc XML,

@.ixml nvarchar(4000)

SELECT @.ixml=(SELECT * FROM SAMPLE "d:SAMPLE" for XML AUTO)

SET @.ixml = 'SET @.Doc.modify(''

declare namespace dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution";

declare namespace d="http://schemas.microsoft.com/office/infopath/2003/ado/dataFields";

insert ('+@.ixml+') into (dfs:myFields/dfs:dataFields)[1] '')'

exec sp_ExecuteSql @.STMT = @.ixml, @.PARAMS = N'@.DOC XML OUT', @.DOC = @.DOC OUT

Since you're using SQL2005 (I know that because of the XML data type), then you can use the new MAX length specification to provide up to 2GB of storage for @.ixml:

nvarchar(MAX)

This is a replacement for the old *LOB ntext data type, but much better because you get the best of both worlds. On one hand, you get the increased capacity that ntext provided, but on the other hand, Large Value Datatypes give you all of the benefits that nvarchar provides (you can compare data, index fields, convert, etc).

varchar(MAX) replaces text
nvarchar(MAX) replaces ntext
varbinary(MAX) replaces image

|||

Thanks’ a lot Jason, it was useful for me, but I wanted to solve another problem:

To modify a XML document this way is too slow, do you know anther way?

|||

You could also create a new XML instance by using FOR XML to bring the relational data and parts of the XML document (extracted with XQuery) together.

Best regards

Michael

How insert more then 4000 characters?

I need insert many dates from tables to XML file, I use T-SQL, everything is OK, but in one step I can insert maximally 4000 character in uni-code.

Is there another way? Thanks' a lot.

I use such sequence T-SQL:

Declare

@.doc XML,

@.ixml nvarchar(4000)

SELECT @.ixml=(SELECT * FROM SAMPLE "d:SAMPLE" for XML AUTO)

SET @.ixml = 'SET @.Doc.modify(''

declare namespace dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution";

declare namespace d="http://schemas.microsoft.com/office/infopath/2003/ado/dataFields";

insert ('+@.ixml+') into (dfs:myFields/dfs:dataFields)[1] '')'

exec sp_ExecuteSql @.STMT = @.ixml, @.PARAMS = N'@.DOC XML OUT', @.DOC = @.DOC OUT

Since you're using SQL2005 (I know that because of the XML data type), then you can use the new MAX length specification to provide up to 2GB of storage for @.ixml:

nvarchar(MAX)

This is a replacement for the old *LOB ntext data type, but much better because you get the best of both worlds. On one hand, you get the increased capacity that ntext provided, but on the other hand, Large Value Datatypes give you all of the benefits that nvarchar provides (you can compare data, index fields, convert, etc).

varchar(MAX) replaces text
nvarchar(MAX) replaces ntext
varbinary(MAX) replaces image

|||

Thanks’ a lot Jason, it was useful for me, but I wanted to solve another problem:

To modify a XML document this way is too slow, do you know anther way?

|||

You could also create a new XML instance by using FOR XML to bring the relational data and parts of the XML document (extracted with XQuery) together.

Best regards

Michael

HOW I CAN SAVE AN XML IN SQL SERVER

I HAVE AN APP THAT RECEIVE FROM THE WEB A XML FILE
I NEED TO SAVE AS A TABLE IN SQL SERVER
CAN ANYBODY HELP ME ?
THANKS
GRS
Do you want to shred it into rows/columns or store it as blob?
If you want to shred it, you may use OpenXML or Dataset. If the file size is
big, consider using Bulkload which will stream the date to SQL server.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"GRS" <g_saturansky@.yahoo.com> wrote in message
news:1121727965.861690.60780@.g47g2000cwa.googlegro ups.com...
>I HAVE AN APP THAT RECEIVE FROM THE WEB A XML FILE
> I NEED TO SAVE AS A TABLE IN SQL SERVER
> CAN ANYBODY HELP ME ?
> THANKS
> GRS
>
|||

Quote:

Originally posted by GRS
I HAVE AN APP THAT RECEIVE FROM THE WEB A XML FILE
I NEED TO SAVE AS A TABLE IN SQL SERVER
CAN ANYBODY HELP ME ?
THANKS
GRS

http://www.kbalertz.com/kb_316244.aspx
That will get you started.
...

how has XML BulkLoad changed for SQL2005 versus SQL 2000

how has XML BulkLoad changed for SQL2005 versus SQL 2000
Do you mean SQLXML as shipped with SQL 2000 (where there was no bulkload
:-)), or SQLXML 3.0 SP3 to SQLXML 4.0? I don't think anything has changed
there except support for the new datatypes (XML, max-types).
Best regards
Michael
"edward" <edward@.discussions.microsoft.com> wrote in message
news:A8D7CB47-3964-43F5-8186-8B6814FC000E@.microsoft.com...
> how has XML BulkLoad changed for SQL2005 versus SQL 2000

how has XML BulkLoad changed for SQL2005 versus SQL 2000

how has XML BulkLoad changed for SQL2005 versus SQL 2000Do you mean SQLXML as shipped with SQL 2000 (where there was no bulkload
:-)), or SQLXML 3.0 SP3 to SQLXML 4.0? I don't think anything has changed
there except support for the new datatypes (XML, max-types).
Best regards
Michael
"edward" <edward@.discussions.microsoft.com> wrote in message
news:A8D7CB47-3964-43F5-8186-8B6814FC000E@.microsoft.com...
> how has XML BulkLoad changed for SQL2005 versus SQL 2000