predicate using xml in sql server 2005

Hi ,
I have a doubt using xml in sql sever 2005.
I found in many places while specifying Xpath ,the predicate contains '1'
like
/Inst:root/Inst:Location[1]/Inst:step .
I tried with some other numbers in predicate like
/Inst:root/Inst:Location[2]/Inst:step .i got null value.
Can u plz explain me what is it doing.
thx.
priya.The square brackets are an abbreviation of the position() XPath function.
E.g.:
/Inst:root/Inst:Location[1]/Inst:step
equals to
/Inst:root/Inst:Location[position() = 1]/Inst:step
Return the Inst:step child-node of the first Inst:Location node in the
Inst:root node. A null value for /Inst:root/Inst:Location[2]/Inst:step means
that there is either only one Inst:Location node or there is no Inst:step
node on the second Inst:Location.
ML
http://milambda.blogspot.com/

Prblem to store XML result into an output veriable on SQL 2000

Hi,

I want to store the result of the query

SELECT * FROM Customer FOR XML AUTO,ELEMENTS

Into an output veriable. How will I do this in SQL Server 2000?

I've tried this in simple way like

declare @.x varchar(1000)

set @.x = (select * from customer for xml auto,elements)

select @.x

This is perfectly working in SQL 2005 but throwing error in 2000

also in I've tried this using cursor, TempTable on SQL Server 2000.

Please help me.

You can't able to do this in SQL Server 2000. In SQL Server 2000 we don't have XML datatype. It is introduced from SQL Server 2005 only.

The only possible solution is manullay concatinating the values, but it is very expensive and there is char length limitation (8000) may cause truncation of your data.

|||

Thank you very much.

Prblem to store XML result into an output veriable on SQL 2000

Hi,

I want to store the result of the query

SELECT * FROM Customer FOR XML AUTO,ELEMENTS

Into an output veriable. How will I do this in SQL Server 2000?

I've tried this in simple way like

declare @.x varchar(1000)

set @.x = (select * from customer for xml auto,elements)

select @.x

This is perfectly working in SQL 2005 but throwing error in 2000

also in I've tried this using cursor, TempTable on SQL Server 2000.

Please help me.

You can't able to do this in SQL Server 2000. In SQL Server 2000 we don't have XML datatype. It is introduced from SQL Server 2005 only.

The only possible solution is manullay concatinating the values, but it is very expensive and there is char length limitation (8000) may cause truncation of your data.

|||

Thank you very much.

PP: XML Variable DataLength returns as 5

Try using
select CAST(@.xmlSourceDestinationAttributes AS VARBINARY(MAX))
and you will see the BOM that is at the beginning of the xml document.
Dan

> set nocount on
> Declare @.xmlSourceDestinationAttributes XML
> Select @.xmlSourceDestinationAttributes = ''
> --Select @.xmlSourceDestinationAttributes
> select Datalength(@.xmlSourceDestinationAttribut
es)Hi Folks
Here is what I found, when I execute this query
set nocount on
Declare @.xmlSourceDestinationAttributes XML
Select @.xmlSourceDestinationAttributes = ''
--Select @.xmlSourceDestinationAttributes
select Datalength(@.xmlSourceDestinationAttribut
es)
5
Question
=======
How come I get a value of 5 even tough I passed nothing.|||Try using
select CAST(@.xmlSourceDestinationAttributes AS VARBINARY(MAX))
and you will see the BOM that is at the beginning of the xml document.
Dan

> set nocount on
> Declare @.xmlSourceDestinationAttributes XML
> Select @.xmlSourceDestinationAttributes = ''
> --Select @.xmlSourceDestinationAttributes
> select Datalength(@.xmlSourceDestinationAttribut
es)sql

PP: XML Variable DataLength returns as 5

Hi Folks
Here is what I found, when I execute this query
set nocount on
Declare @.xmlSourceDestinationAttributes XML
Select @.xmlSourceDestinationAttributes = ''
--Select @.xmlSourceDestinationAttributes
select Datalength(@.xmlSourceDestinationAttributes)
--
5
Question
======= How come I get a value of 5 even tough I passed nothing.Try using
select CAST(@.xmlSourceDestinationAttributes AS VARBINARY(MAX))
and you will see the BOM that is at the beginning of the xml document.
Dan
> set nocount on
> Declare @.xmlSourceDestinationAttributes XML
> Select @.xmlSourceDestinationAttributes = ''
> --Select @.xmlSourceDestinationAttributes
> select Datalength(@.xmlSourceDestinationAttributes

Posting to SQLXML

Hi,
I'm new to SQLXML and have run into a problem when using it
to update SQL tables from our application database.
We are generating XML in our application db and http
posting it to update an SQL database. The
(attribute-centric) XML generated matches the target table,
the element name is the same as the table name and the
attributes match the columns to be updated.
<ROOT>
<SUPPINV TABLE_KEY="10542049*202.12" INVOICE_TOTAL="377.56"
INVOICE_NUMBER="124577" INVOICE_DATE_KEY="12475">
<SUPPINV TABLE_KEY="123 ... >
</ROOT>
We have to URLEncode the XML then using HTTP post, we post
directly to the SQLXML URL from the application db.
This works as long as the size of posted data does not
exceed about 35kb and is not between 4 & 8Kb. This means I
have to chunk the XML into about 40 rows per post.
Also after posting about 100,000 rows (at 40 per time) or
so I generally get an HTTP 500 error.
Can anyone explain what is going on here and perhaps
suggest a way to improve this.
Regards,
StuartDid you limit the post size in SqlXml? What about the timeout in IIS?
Could be the request is just taking too long and timing out...
Irwin
"Stuart" <newplanet_x@.yahoo.com.au> wrote in message
news:1cf801c50fe8$3fea0da0$a601280a@.phx.gbl...
> Hi,
> I'm new to SQLXML and have run into a problem when using it
> to update SQL tables from our application database.
> We are generating XML in our application db and http
> posting it to update an SQL database. The
> (attribute-centric) XML generated matches the target table,
> the element name is the same as the table name and the
> attributes match the columns to be updated.
> <ROOT>
> <SUPPINV TABLE_KEY="10542049*202.12" INVOICE_TOTAL="377.56"
> INVOICE_NUMBER="124577" INVOICE_DATE_KEY="12475">
> <SUPPINV TABLE_KEY="123 ... >
> </ROOT>
> We have to URLEncode the XML then using HTTP post, we post
> directly to the SQLXML URL from the application db.
> This works as long as the size of posted data does not
> exceed about 35kb and is not between 4 & 8Kb. This means I
> have to chunk the XML into about 40 rows per post.
> Also after posting about 100,000 rows (at 40 per time) or
> so I generally get an HTTP 500 error.
> Can anyone explain what is going on here and perhaps
> suggest a way to improve this.
> Regards,
> Stuartsql

Posting to SQLXML

Hi,
I'm new to SQLXML and have run into a problem when using it
to update SQL tables from our application database.
We are generating XML in our application db and http
posting it to update an SQL database. The
(attribute-centric) XML generated matches the target table,
the element name is the same as the table name and the
attributes match the columns to be updated.
<ROOT>
<SUPPINV TABLE_KEY="10542049*202.12" INVOICE_TOTAL="377.56"
INVOICE_NUMBER="124577" INVOICE_DATE_KEY="12475">
<SUPPINV TABLE_KEY="123 ... >
</ROOT>
We have to URLEncode the XML then using HTTP post, we post
directly to the SQLXML URL from the application db.
This works as long as the size of posted data does not
exceed about 35kb and is not between 4 & 8Kb. This means I
have to chunk the XML into about 40 rows per post.
Also after posting about 100,000 rows (at 40 per time) or
so I generally get an HTTP 500 error.
Can anyone explain what is going on here and perhaps
suggest a way to improve this.
Regards,
Stuart
Did you limit the post size in SqlXml? What about the timeout in IIS?
Could be the request is just taking too long and timing out...
Irwin
"Stuart" <newplanet_x@.yahoo.com.au> wrote in message
news:1cf801c50fe8$3fea0da0$a601280a@.phx.gbl...
> Hi,
> I'm new to SQLXML and have run into a problem when using it
> to update SQL tables from our application database.
> We are generating XML in our application db and http
> posting it to update an SQL database. The
> (attribute-centric) XML generated matches the target table,
> the element name is the same as the table name and the
> attributes match the columns to be updated.
> <ROOT>
> <SUPPINV TABLE_KEY="10542049*202.12" INVOICE_TOTAL="377.56"
> INVOICE_NUMBER="124577" INVOICE_DATE_KEY="12475">
> <SUPPINV TABLE_KEY="123 ... >
> </ROOT>
> We have to URLEncode the XML then using HTTP post, we post
> directly to the SQLXML URL from the application db.
> This works as long as the size of posted data does not
> exceed about 35kb and is not between 4 & 8Kb. This means I
> have to chunk the XML into about 40 rows per post.
> Also after posting about 100,000 rows (at 40 per time) or
> so I generally get an HTTP 500 error.
> Can anyone explain what is going on here and perhaps
> suggest a way to improve this.
> Regards,
> Stuart

Posting an entire XML document to SQL Server

How do I post an entire XML document to SQL Server in a such a way that when
it gets posted, I can process it with a Stored Procedure?
My specific application is using BizTalk 2004 to send an entire document to
SQL Server without using the "updategram" schema. I just want to send an
entire document via a BizTalk 2004 SQL Send Port.
Thanks,
Mike Jansen
Sr. Software Developer
Prime ProData, Inc.
North Canton, Ohio USA
(mjansen) (at) (primepro-com)
In SQL Server 2000, define your stored proc with a parameter of type NTEXT
or TEXT and use your favorite provider to pass the XML (in an encoding that
is compatible with either Unicode (NTEXT) or your server code page (TEXT)).
Best regards
Michael
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:uinFg5SvEHA.4020@.TK2MSFTNGP10.phx.gbl...
> How do I post an entire XML document to SQL Server in a such a way that
> when
> it gets posted, I can process it with a Stored Procedure?
> My specific application is using BizTalk 2004 to send an entire document
> to
> SQL Server without using the "updategram" schema. I just want to send an
> entire document via a BizTalk 2004 SQL Send Port.
> Thanks,
> Mike Jansen
> Sr. Software Developer
> Prime ProData, Inc.
> North Canton, Ohio USA
> (mjansen) (at) (primepro-com)
>

PostgreSQL to XML!

Hi, everybody!.
I=B4m very new using XML and PostgreSQL so I have some questions:
1) I would like to know if there is a way to convert a query SQL and
return the request in an XML document. I=B4m using PostgreSQL to
devoloping the database. How I could to do this?
2) What is the principal advantage to do this?
3) Somebody know if there is somebody working with this? (study case,
articles, papers, journals, universities).
Thanks.
JoanaJoana G. Malaverri wrote:
> Hi, everybody!.
> Im very new using XML and PostgreSQL so I have some questions:
> 1) I would like to know if there is a way to convert a query SQL and
> return the request in an XML document. Im using PostgreSQL to
> devoloping the database. How I could to do this?
See the documentation
<URL:http://www.postgresql.org/docs/8.2/...tatype-xml.html>.
Note that this newsgroup is about XML in Microsoft SQL Server, if you
need further help with Postgres then a newsgroup, forum or mailing list
dedicated to Postgres seems a better place to look for.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/

Post XML Data with SQLServer

Hi,
I want to "post" Data in XML Format to a web server with SQL Server 2000.
How can I do that?
Hi
Depending on what exaclty is required then take a look at the following and
other articles on the site:
http://sqlxml.org/faqs.aspx?faq=15
John
"Mehmet" <Mehmet@.discussions.microsoft.com> wrote in message
news:178F3F0E-42CF-41B1-9C30-45C8CD51C851@.microsoft.com...
> Hi,
> I want to "post" Data in XML Format to a web server with SQL Server 2000.
> How can I do that?
|||Hi,
First of all thanks.But I want to post xml data with a SQLServerAgent-Job to a web server or url.
|||Hi
If you are using an agent job, the I would expect you to be writing to XML
files. Try: http://sqlxml.org/faqs.aspx?faq=10
John
"Mehmet" <Mehmet@.discussions.microsoft.com> wrote in message
news:799D9529-78EA-445A-BC7E-16EDD100C56E@.microsoft.com...
> Hi,
> First of all thanks.But I want to post xml data with a SQLServerAgent-Job
to a web server or url.

Possible? SQL Svr obtaining data via SOAP/XML from another SQL Sv

Hello All,
My challenge is to create a process that will bring together data from two
SQL servers (one local, one remote through the internet). The task is to
create a Stored Procedure that will perform a relational query based on data
from the two databases and return the resulting record set to a Crystal
Report. The remote database is part of our company's CRM HOSTED solution
which provides data accessible via a SOAP/XML interface.
I have implemented solutions in the past using a SOAP/XML data feed, whereby
I used VBScript, MSXML, MSSOAP to connect, query, parse and insert data into
an SQL database. For this implementation, however, I want to avoid this
method at all costs since there would be a tremendous overhead of converting,
maintaining and updating dozens of Crystal Reports to this new environment.
Instead, I would like to be able to make an SQL Stored Procedure invoke the
SOAP call and, in turn, load and process the resulting XML dataset as though
it were an SQL Record Set. I have found technical leads supporting the theory
that I might be able to achieve this goal. The following are technical
"bread-crumbs" I found or technical areas I have investigated thus far:
(1) SOAP/XML can be delivered from an MSSQL server in several ways via "AS
XML" functionality provided in SELECT verb. I am hypothesizing that the CRM
can deliver the XML data per a direct SQL query through this method. The nut
to crack here is that our XML data would be delivered back to the caller
"shrink-wrapped" in a SOAP Envelope and thus there would be the task of
unwrapping the XML dataset from the SOAP envelope upon the return of data to
the caller.
(2) MSSQL provides a method via "OPENXML" to open and parse an XML source
file and associate data returned with SELECT / Relational logic. CAVEAT: I
have only seen the OPENXML deal with XML data stored in FILE resources...not
an XML object such as that found implemented in the MSXML object classes
which I have used in VBSCRIPT.
I am trying to efficiently architect a solution without having to develop
too many components and excessive rework of prior development. My goal is to
perform this data query task ALL within the MSSQL environment. Any direction
or validation is greatly appreciated.
Sincerely,
Karl Engvold
Website: http://www.mcint.net
In SQLServer 2005 we have Native Web Services Support. This allows one to expose a stored procedure as a web service. The stored procedcure could be written in CLR or T-SQL. We also generate WSDL on demand. The SOAP responses contain "rowsets" to captu
re the output of select statements. These rowsets use the diffgram format on the wire and as such can be mapped to datasets on the client. Using a VS>net proxy generator to generate the client code does this automatically for you.
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||as mentioned earlier this fucntionality is available in SQL2005 Beta bits. If you can get a hold of these bits and .Net framewwork Beta1 you can do what I suggested.
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

possible uses and impact of using xml datatype

I'm looking at this for an application I'm working on right now that is currently using SQL 2000 and a huge number of meta data files.

We synchronize externally using different technologies to items which cannot be pre-defined at all. We don't know what they will look like, what attributes they will have, or even if a new item might pop up.

So. we have a database just to record that "x" exists, and there is an applicaiton layer to interpret with the meta data what x actually means and looks like, then display it to the user. We track changes to "x" once we know it is there, over time. IT's attribute values will change over time.

I'm thinking with 2005, we could stuff the fact that "x" exists into a row, and it's corresponding definition in an XML column. It seems that this is the exact situation the XML data type was invented for.

My question is: am I right in the above assumption, and what would we really gain by moving that information from the filesystem into the database? Better performance? Easier to manipulate the XML? Easier association of a particular XML file to database data? Would it degrade performance of a system that is currently kind of slow but working?

I think if we used this correctly and in a limited way, we could have something pretty spiffy.

How easy is it to read and manipulate the elements in the XML using SQL?

I want to stay away from CLR and continue using the application layer, just have the attributes for the object available to the application layer.

Could someone give me an example of the ideal situation this datatype was invented for? I believe it would be wrong to invent the whole "database-in-a-database" thing, but for our purposes the datatype might work, since we have no control over the entities or attributes but need to store their existence for the UI.

Obviously, I have a lot of research to do but I thought I might ask if it's worth my time at this point.

Yes, it's worth your time to investigate, but it's hard to know what the impact might be on your particular situation.

Tagged data formats and markup languages generally, of which XML is a member, are ideal for metadata situations.

But the thing is, you must already *have* a metadata language in your app, so it's hard to say what benefit would come from changing it to XML ... except for exactly this point, that the xpath and xquery capabilities in XML generally, and the excellent integration with SQL in SQL Server 2005, are very likely to be helpful. Also that XML as a language, is simple, straightforward, and very widespread.

Possible to OPENXML() from URL?

Is it possible to get the XML Document for a call to OPENXML() from a URL
directly?
Example: I have a .NET web service that returns XML based on parameters
submitted to the URL. I'd like to be able to write a TSQL batch that gets
XML documents from this server and processes them using OPENXML to update
rows in a SQL table.
Anyone ever done something like this?
TIA
Paul
Have you considered using an XML template? Templates accept parameters, much in the same way as stored procedures.
I've written an article on this topic, which you can find at the following URL:
http://www.sqljunkies.com/Article/53...3C8ECACDC.scuk
Hope this helps!
Denise.
Denise E. White
Technical Director
The Next Version UK
www.thenextversion.com
www.denisewhite.co.uk

Possible to disable welll-formedness checking when inserting XML data.

Hi All,

Can anyone help with the following problem? I have a database which
contains a table with a 'text' field, and the text field contains an
xml document - typically 50-100K. Now I'd like to make use of SQL
Server 2005s XMLData type. To do this I have created a new field of
the 'xmldata' datatype, and run an SQL statement to update the contents
from one field to another - hoping to end up with a complete table of
xml (based on the old text field).

The problem I have is after a minute or so, it must come across an
badly-formed xml fragment because I get the following message:

Msg 9436, Level 16, State 1, Line 1
XML parsing: line 1, character 67640, end tag does not match start tag

Can I turn off the checking during the update, or is it not possible to
add badly formed data to the xmldata field. Any help appreciated as
the table runs into tens of thousands of rows, so I can't really check
the contents of each!

Many thanks,

Duncan.(DSmith1974@.googlemail.com) writes:
> Can anyone help with the following problem? I have a database which
> contains a table with a 'text' field, and the text field contains an
> xml document - typically 50-100K. Now I'd like to make use of SQL
> Server 2005s XMLData type. To do this I have created a new field of
> the 'xmldata' datatype, and run an SQL statement to update the contents
> from one field to another - hoping to end up with a complete table of
> xml (based on the old text field).
> The problem I have is after a minute or so, it must come across an
> badly-formed xml fragment because I get the following message:
> Msg 9436, Level 16, State 1, Line 1
> XML parsing: line 1, character 67640, end tag does not match start tag
> Can I turn off the checking during the update, or is it not possible to
> add badly formed data to the xmldata field. Any help appreciated as
> the table runs into tens of thousands of rows, so I can't really check
> the contents of each!

Indeed, you can only pass valid XML fragments to the xml data type. They
don't have to be valid documents, that is have exactly one top-level tag,
but apart from that they must follow the XML syntax. The reason is that
the XML is stored an internal format, so SQL Server have no idea of what
do with the poorly formed XML.

--
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