How to receive files in FTP task

Hi everyone,

I want to design a FTP task to download all the xml files from a FTP site. And I don't know what the file's name is.

How can I design this task?

Thank you for your helps!

Tony

Hi, you can use wildcards for the remote path property. For example "/testfiles/*.*"

Hope this helps

|||

Hi Graig,

Thank you for your reply. The wildcards doesn't work. The error is

"File represented by "/users/*.*" does not exist"

It seems I have to pick a specific file name, but I need to download the whole folder's files.

Tony

|||

Sorry Craig, I made a small mistake:-P. It works! Thank you so much!

Tony

|||Can you share what mistake you made? I have the same problem and would like to learn too.

How to receive files in FTP task

Hi everyone,

I want to design a FTP task to download all the xml files from a FTP site. And I don't know what the file's name is.

How can I design this task?

Thank you for your helps!

Tony

Hi, you can use wildcards for the remote path property. For example "/testfiles/*.*"

Hope this helps

|||

Hi Graig,

Thank you for your reply. The wildcards doesn't work. The error is

"File represented by "/users/*.*" does not exist"

It seems I have to pick a specific file name, but I need to download the whole folder's files.

Tony

|||

Sorry Craig, I made a small mistake:-P. It works! Thank you so much!

Tony

|||Can you share what mistake you made? I have the same problem and would like to learn too.

How to read XML if I have no OWC?

Dear all,
I have a XML file which is generated from pivot table (using
"pivottable.xmldata" command to generate). Now, I want to read this file in
another computer. However, there is no office web component (OWC) in this
computer. I don't want to buy any commercial product to view the data. So,
how can I read the data in this file? Can I use datagrid to display the data?
Thanks!
Polly
Your best bet is probably to apply an XSLT stylesheet to render the XML data
as HTML. See
http://msdn.microsoft.com/library/de...ml/conXSLT.asp
for more information.
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Polly" <Polly@.discussions.microsoft.com> wrote in message
news:38E539D8-B5F6-4478-9277-84EC06152FAA@.microsoft.com...
Dear all,
I have a XML file which is generated from pivot table (using
"pivottable.xmldata" command to generate). Now, I want to read this file in
another computer. However, there is no office web component (OWC) in this
computer. I don't want to buy any commercial product to view the data. So,
how can I read the data in this file? Can I use datagrid to display the
data?
Thanks!
Polly

How to read XML data from SQL data field?

Using RS2000

I have a field in a SQL DB which contains data stored as an XML string. Is there an easy way to parse this string into data without moving to SS2005?

I have builts a parser in SQL, but I would like to know if RS had a way to directly read this. If not, is there a method in RS2005 which handles this type of data?

The only way to really do this in RS2000 is to create your own custom data processing extension that encapsulates the code you have written to parse the Xml. There are quite a few examples online.

In RS2005, you can use the Xml Data Processing Extension, which is designed for retieving data from xml data sources. It retrieves the data from an xml document which is retireved from a web service, web page, or can use a document embedded in the Xml Query. This extension is very powerful, but can also be somewhat complex for advanced queries.

For more information: http://msdn2.microsoft.com/en-us/library/ms159741.aspx|||

you can write a function in report Code section that will take the xml string, load it into System.Xml.XmlDocument and parse it.

<?xml version="1.0" encoding="utf-8"?>

<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">

<DataSources>

<DataSource Name="DataSource1">

<ConnectionProperties>

<IntegratedSecurity>true</IntegratedSecurity>

<ConnectString>server=.</ConnectString>

<DataProvider>SQL</DataProvider>

</ConnectionProperties>

<rd:DataSourceID>6cfe005e-c5e0-4297-aa6b-f2ca0673ec05</rd:DataSourceID>

</DataSource>

</DataSources>

<BottomMargin>1in</BottomMargin>

<RightMargin>1in</RightMargin>

<rd:DrawGrid>true</rd:DrawGrid>

<InteractiveWidth>8.5in</InteractiveWidth>

<rd:SnapToGrid>true</rd:SnapToGrid>

<Body>

<ReportItems>

<List Name="list1">

<Left>1.25in</Left>

<ReportItems>

<Textbox Name="my_xml_1">

<Left>0.5in</Left>

<Top>0.5in</Top>

<rd:DefaultName>my_xml_1</rd:DefaultName>

<Width>3.375in</Width>

<Style>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Height>0.25in</Height>

<Value>=Code.ParseXml(Fields!my_xml.Value)</Value>

</Textbox>

</ReportItems>

<DataSetName>DataSet1</DataSetName>

<Top>0.375in</Top>

<Height>1in</Height>

</List>

</ReportItems>

<Height>2in</Height>

</Body>

<rd:ReportID>c9ad02b9-b7a2-4780-b954-3ebaa11e0afb</rd:ReportID>

<LeftMargin>1in</LeftMargin>

<DataSets>

<DataSet Name="DataSet1">

<Query>

<rd:UseGenericDesigner>true</rd:UseGenericDesigner>

<CommandText>select '&lt;root&gt;Hello world!&lt;/root&gt;' my_xml

union select '&lt;root&gt;Goodbye!&lt;/root&gt;'</CommandText>

<DataSourceName>DataSource1</DataSourceName>

</Query>

<Fields>

<Field Name="my_xml">

<rd:TypeName>System.String</rd:TypeName>

<DataField>my_xml</DataField>

</Field>

</Fields>

</DataSet>

</DataSets>

<CodeModules>

<CodeModule>System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</CodeModule>

</CodeModules>

<Code>public shared Function ParseXml(xml as string) as string

Dim doc as System.Xml.XmlDocument = new System.Xml.XmlDocument()

doc.LoadXml(xml)

ParseXml = doc.SelectSingleNode("root").InnerText

end function</Code>

<Width>6.5in</Width>

<InteractiveHeight>11in</InteractiveHeight>

<Language>en-US</Language>

<TopMargin>1in</TopMargin>

</Report>

How to read the xml returned by select query with "For XML Auto,Elements"

Hi all,

I am writing a select query which produces huge xml data.Now i want to read that data from my web application a save it as xml file.

How can i do that.

I am using asp.net vb.net.

Check if this post is helpful.

http://forums.asp.net/t/1182514.aspx

how to read multiple records in a xml file using openxml

I have a xml like:

<Member ID="123">
<DateBorrowed>11-01-2006</DateBorrowed>
<Book ID="222"
Title=""ABC">
<Category> Fiction</Category>
</Book>
<Book ID="333"
Title=""ABu">
<Category>Children</Category>
</Book>
......
</Member>

I used OPENXML to read into MS SQL server database table, but it only reads first book. What should I do to read all three books, and insert 2 lines into the table:

MemberID | DateBorrowed | BookID | BookTitle | BookCategory
123 11-01-2006 222 ABC Fiction
123 11-01-2006 333 ABu Children
......

My code following only read first book:

CREATE PROCEDURE sp_insert_RefundCorrectionAcceptance AS

DECLARE @.iTree int
DECLARE @.xmlFile VARCHAR(2000)

EXEC sp_xml_preparedocument @.iTree OUTPUT, @.xmlFile,
SELECT *
FROM OPENXML (@.iTree,'root', 3)
WITH (
MemberID decimal '/Member/@.ID',
DateBorrowed datetime 'DateBorrowed/text()',
BookID decimal '/book/@.id',
bookTitle varchar(200) '/book/@.title',
bookCat varchar(100) '/book/category/text()'

)
EXEC sp_xml_removedocument @.iTree
GO

Thanks

I hop it will work for you.........

Code Snippet

DECLARE @.iTree int

DECLARE @.xmlFile VARCHAR(2000)

set @.xmlFile='

<Member ID="123">

<DateBorrowed>11-01-2006</DateBorrowed>

<Book ID="222" Title="ABC">

<Category> Fiction</Category>

</Book>

<Book ID="333" Title="ABu">

<Category>Children</Category>

</Book>

<Book ID="334" Title="XYZ">

<Category>XYZ</Category>

</Book>

</Member>

'

EXEC sp_xml_preparedocument @.iTree OUTPUT, @.xmlFile

SELECT * FROM OPENXML (@.iTree,'/Member/Book/Category',2)

WITH (

MemberID decimal 'http://@.ID',

DateBorrowed datetime 'http://DateBorrowed/text()',

BookID decimal '../@.ID',

bookTitle varchar(200) '../@.Title',

bookCat varchar(100) 'text()'

)

EXEC sp_xml_removedocument @.iTree

GO

Happy Programming Dear...

how to read an xml file on local file system

Hi,
i have a xml file c:\abcd.xml in my local filesystem.. i just want to
take the path as in parameter for a stored procedure and insert the values
into a table type variable
execute usp_xx (@.xmlpath='c:\abcd.xml')
-- inside the sp i would like to read the xml file and insert the same
into a table type variable..
Can anyone tell me the idea..
nsr
Message posted via http://www.webservertalk.comYou should read the file on the mid-tier/client and send the data as a
stored proc parameter value in SQL Server 2000. SQL Server 2005 will have a
way to read from files...
Best regards
Michael
"narasimha rao chandramouli via webservertalk.com"
<forum@.nospam.webservertalk.com> wrote in message
news:7a34860ffbc045ddaad40506b15e2a1b@.SQ
webservertalk.com...
> Hi,
> i have a xml file c:\abcd.xml in my local filesystem.. i just want to
> take the path as in parameter for a stored procedure and insert the values
> into a table type variable
> execute usp_xx (@.xmlpath='c:\abcd.xml')
> -- inside the sp i would like to read the xml file and insert the same
> into a table type variable..
> Can anyone tell me the idea..
>
> nsr
> --
> Message posted via http://www.webservertalk.com

how to read an xml file on local file system

Hi,
i have a xml file c:\abcd.xml in my local filesystem.. i just want to
take the path as in parameter for a stored procedure and insert the values
into a table type variable
execute usp_xx (@.xmlpath='c:\abcd.xml')
-- inside the sp i would like to read the xml file and insert the same
into a table type variable..
Can anyone tell me the idea..
nsr
Message posted via http://www.droptable.com
You should read the file on the mid-tier/client and send the data as a
stored proc parameter value in SQL Server 2000. SQL Server 2005 will have a
way to read from files...
Best regards
Michael
"narasimha rao chandramouli via droptable.com"
<forum@.nospam.droptable.com> wrote in message
news:7a34860ffbc045ddaad40506b15e2a1b@.droptable.co m...
> Hi,
> i have a xml file c:\abcd.xml in my local filesystem.. i just want to
> take the path as in parameter for a stored procedure and insert the values
> into a table type variable
> execute usp_xx (@.xmlpath='c:\abcd.xml')
> -- inside the sp i would like to read the xml file and insert the same
> into a table type variable..
> Can anyone tell me the idea..
>
> nsr
> --
> Message posted via http://www.droptable.com

How to read a XML file from a stored procedure?

From a stored procedure, I would like to import into a varchar variable the
content of a
XML file before I invoke the sp_xml_preparedocument SP. If possible, I want
to avoid to
first have to import the XML file into a table.
Is it possible at all with SQL 2000 SP4 to directly import a XML file? How?
thanks.It seems XQuery is one way as I'm starting to read this article [1]
<%= Clinton Gallagher
[1] http://www.idealliance.org/proceedi.../XQueryRDS.html
"Gaetan" <me@.somewhere.com> wrote in message
news:4eqd129irfvdj5fem8v118jt9d6qd2gibm@.
4ax.com...
> From a stored procedure, I would like to import into a varchar variable
> the content of a
> XML file before I invoke the sp_xml_preparedocument SP. If possible, I
> want to avoid to
> first have to import the XML file into a table.
> Is it possible at all with SQL 2000 SP4 to directly import a XML file?
> How?
> thanks.|||XQuery could have been an avenue to explore had not been for the fact that I
'm stuck with
SQL 2000 SP4 for a while longer.
On Tue, 14 Mar 2006 10:26:15 -0600, "clintonG"
< csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote:

>It seems XQuery is one way as I'm starting to read this article [1]
><%= Clinton Gallagher
>[1] http://www.idealliance.org/proceedi.../XQueryRDS.html
>|||You would have to write an extended stored procedure that reads the data
from a file. SQL Server 2005 has native ability to read from a file but not
SQL Server 2000.
My recommendation is to read the file in the client code and pass it to the
database as a stored procedure parameter...
Best regards
Michael
"Gaetan" <me@.somewhere.com> wrote in message
news:4eqd129irfvdj5fem8v118jt9d6qd2gibm@.
4ax.com...
> From a stored procedure, I would like to import into a varchar variable
> the content of a
> XML file before I invoke the sp_xml_preparedocument SP. If possible, I
> want to avoid to
> first have to import the XML file into a table.
> Is it possible at all with SQL 2000 SP4 to directly import a XML file?
> How?
> thanks.|||we are haveing a similar issue attempting to read xml through our own
firewall, (we can only have sql port and port 80 available) so no fileopen
methods are available:
so we cant use vbnet:readxml method as the filepath violates firewall
security: it is becoming a showstopper for our solution . if we could
paramaterise a sp passing in the filename problem would be solved.
All we have found so far is a declare @.doc; exec @.doc = cmdShell
and execute a dos "type" command to type out the contents into the variable.
There are several examples of this method on the net.
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:uoqgZ$uTGHA.1708@.TK2MSFTNGP14.phx.gbl...
> You would have to write an extended stored procedure that reads the data
> from a file. SQL Server 2005 has native ability to read from a file but
> not SQL Server 2000.
> My recommendation is to read the file in the client code and pass it to
> the database as a stored procedure parameter...
> Best regards
> Michael
> "Gaetan" <me@.somewhere.com> wrote in message
> news:4eqd129irfvdj5fem8v118jt9d6qd2gibm@.
4ax.com...
>

How to read a XML file from a stored procedure?

From a stored procedure, I would like to import into a varchar variable the content of a
XML file before I invoke the sp_xml_preparedocument SP. If possible, I want to avoid to
first have to import the XML file into a table.
Is it possible at all with SQL 2000 SP4 to directly import a XML file? How?
thanks.
It seems XQuery is one way as I'm starting to read this article [1]
<%= Clinton Gallagher
[1] http://www.idealliance.org/proceedin...XQueryRDS.html
"Gaetan" <me@.somewhere.com> wrote in message
news:4eqd129irfvdj5fem8v118jt9d6qd2gibm@.4ax.com...
> From a stored procedure, I would like to import into a varchar variable
> the content of a
> XML file before I invoke the sp_xml_preparedocument SP. If possible, I
> want to avoid to
> first have to import the XML file into a table.
> Is it possible at all with SQL 2000 SP4 to directly import a XML file?
> How?
> thanks.
|||XQuery could have been an avenue to explore had not been for the fact that I'm stuck with
SQL 2000 SP4 for a while longer.
On Tue, 14 Mar 2006 10:26:15 -0600, "clintonG"
<csgallagher@.REMOVETHISTEXTmetromilwaukee.com> wrote:

>It seems XQuery is one way as I'm starting to read this article [1]
><%= Clinton Gallagher
>[1] http://www.idealliance.org/proceedin...XQueryRDS.html
>
|||You would have to write an extended stored procedure that reads the data
from a file. SQL Server 2005 has native ability to read from a file but not
SQL Server 2000.
My recommendation is to read the file in the client code and pass it to the
database as a stored procedure parameter...
Best regards
Michael
"Gaetan" <me@.somewhere.com> wrote in message
news:4eqd129irfvdj5fem8v118jt9d6qd2gibm@.4ax.com...
> From a stored procedure, I would like to import into a varchar variable
> the content of a
> XML file before I invoke the sp_xml_preparedocument SP. If possible, I
> want to avoid to
> first have to import the XML file into a table.
> Is it possible at all with SQL 2000 SP4 to directly import a XML file?
> How?
> thanks.

How to query xml data in column type xml

How can I query xml data in column type xml using an xml schema from the same table row in the xmlschema column and return values(not xml) to insert into a seperate table

XML Data Table

C1 =RecordID (int)

C2 =XMLData (xml)

C3 = XMLSchema(xml)

How can I query the XML data using the xmlshema column

and return values(not xml) to insert into table 2

Table 2

C1 = RecordID

C2 = User

C3 = date

thanks

I think you are looking for the XQuery "nodes()" function if I understand you correctly. It sounds like you want to reach into the column that is storing the schema, extract scalar values from that schema and store it in a relational table. If this is what you are trying to do, then the nodes() function should work for you.

Here is an example using the nodes() function on the xml data type.

http://msdn2.microsoft.com/en-us/ms188282.aspx

Once you get the data into relational form, you can CURSOR over it and call your INSERT statement with parameters, use INSERT INTO ... SELECT, or generate dynamic SQL.

How to query XML

This is the content of one of the rows of the Message column.
<ERINOT VersionMajor="1" VersionMinor="1"
xmlns:mstns="http://tempuri.org/Dataset1.xsd">
<MessageId>
<SenderId>900013581</SenderId>
<ReceiverId>900013574</ReceiverId>
<GenerationDateTime>2005-09-15T05:54:01</GenerationDateTime>
<MessageType>PAS</MessageType>
<MessageNo>22412022</MessageNo>
<MessageFunction>9</MessageFunction>
</MessageId>
</ERINOT>
How can I query let's say the MessageType?
I tried this:
SET ARITHABORT ON
SELECT ID, Message.value('(/ERINOT/MessageId/@.MessageType)[0]',
'varchar(40)') as MType
FROM ERINOT
But this does not work
Any suggestion is higly appreciated,
Erik
E!k Visser wrote:
> This is the content of one of the rows of the Message column.
> <ERINOT VersionMajor="1" VersionMinor="1"
> xmlns:mstns="http://tempuri.org/Dataset1.xsd">
> <MessageId>
> <SenderId>900013581</SenderId>
> <ReceiverId>900013574</ReceiverId>
> <GenerationDateTime>2005-09-15T05:54:01</GenerationDateTime>
> <MessageType>PAS</MessageType>
> <MessageNo>22412022</MessageNo>
> <MessageFunction>9</MessageFunction>
> </MessageId>
> </ERINOT>
> How can I query let's say the MessageType?
> I tried this:
> SET ARITHABORT ON
> SELECT ID, Message.value('(/ERINOT/MessageId/@.MessageType)[0]',
> 'varchar(40)') as MType
> FROM ERINOT
@.MessageType selects an attribute while you have a MessageType element.
Furthermore with XPath/XQuery the first node has index 1 not 0 so use
(/ERINOT/MessageId/MessageType)[1]

Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/
|||Or if your XML is not constrained by a schema, the following is more
efficient by directly accessing the text node:
(/ERINOT/MessageId/MessageType/text())[1]
Best regards
Michael
"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:ObjdJ2osHHA.2164@.TK2MSFTNGP02.phx.gbl...
> E!k Visser wrote:
> @.MessageType selects an attribute while you have a MessageType element.
> Furthermore with XPath/XQuery the first node has index 1 not 0 so use
> (/ERINOT/MessageId/MessageType)[1]
>
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/
|||Michael,
Thanks for your contribution, but I do not understand what would be the
difference.
What would the query look like in you scenario?
Perhaps good to know I might be limited by the fact I have to use ADO to
access the database.
Erik
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> schreef in bericht
news:uexsXRpsHHA.412@.TK2MSFTNGP04.phx.gbl...
> Or if your XML is not constrained by a schema, the following is more
> efficient by directly accessing the text node:
> (/ERINOT/MessageId/MessageType/text())[1]
> Best regards
> Michael
> "Martin Honnen" <mahotrash@.yahoo.de> wrote in message
> news:ObjdJ2osHHA.2164@.TK2MSFTNGP02.phx.gbl...
>
|||Martin,
"Martin Honnen" <mahotrash@.yahoo.de> schreef in bericht
news:ObjdJ2osHHA.2164@.TK2MSFTNGP02.phx.gbl...
> E!k Visser wrote:
> @.MessageType selects an attribute while you have a MessageType element.
> Furthermore with XPath/XQuery the first node has index 1 not 0 so use
> (/ERINOT/MessageId/MessageType)[1]
Thank you very much! This works like a charm.
But no I have another question, how can I list all nodes?
This could be the XML:
<ContainerMatrixes>
<ContainerMatrix>
<ContRange>RNG20</ContRange>
<Number>0</Number>
<ContStatus>6</ContStatus>
</ContainerMatrix>
<ContainerMatrix>
<ContRange>RNG30</ContRange>
<Number>0</Number>
<ContStatus>6</ContStatus>
</ContainerMatrix>
<ContainerMatrix>
<ContRange>RNG40</ContRange>
<Number>0</Number>
<ContStatus>6</ContStatus>
</ContainerMatrix>
</ContainerMatrixes>
And now I Like to query the ContRange and have three rows as result. It
should be as generic as possible, as in this case a ship usually carries
more than three containers...
Any suggestions?
Erik
|||E!k Visser wrote:

> But no I have another question, how can I list all nodes?
> This could be the XML:
> <ContainerMatrixes>
> <ContainerMatrix>
> <ContRange>RNG20</ContRange>
> <Number>0</Number>
> <ContStatus>6</ContStatus>
> </ContainerMatrix>
> <ContainerMatrix>
> <ContRange>RNG30</ContRange>
> <Number>0</Number>
> <ContStatus>6</ContStatus>
> </ContainerMatrix>
> <ContainerMatrix>
> <ContRange>RNG40</ContRange>
> <Number>0</Number>
> <ContStatus>6</ContStatus>
> </ContainerMatrix>
> </ContainerMatrixes>
> And now I Like to query the ContRange and have three rows as result.
Here is an example, use the nodes method to shred the XML into rows,
then use one of the methods query or value to extract values.
DECLARE @.x XML;
SET @.x = ' <ContainerMatrixes>
<ContainerMatrix>
<ContRange>RNG20</ContRange>
<Number>0</Number>
<ContStatus>6</ContStatus>
</ContainerMatrix>
<ContainerMatrix>
<ContRange>RNG30</ContRange>
<Number>0</Number>
<ContStatus>6</ContStatus>
</ContainerMatrix>
<ContainerMatrix>
<ContRange>RNG40</ContRange>
<Number>0</Number>
<ContStatus>6</ContStatus>
</ContainerMatrix>
</ContainerMatrixes>';
SELECT T.ContRange.query('.')
FROM @.x.nodes('ContainerMatrixes/ContainerMatrix/ContRange') AS
T(ContRange);
Result is
<ContRange>RNG20</ContRange>
<ContRange>RNG30</ContRange>
<ContRange>RNG40</ContRange>

Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/
|||Martin,

> Here is an example, use the nodes method to shred the XML into rows, then
> use one of the methods query or value to extract values.
> DECLARE @.x XML;
> SET @.x = ' <ContainerMatrixes>
> <ContainerMatrix>
> <ContRange>RNG20</ContRange>
> <Number>0</Number>
> <ContStatus>6</ContStatus>
> </ContainerMatrix>
> <ContainerMatrix>
> <ContRange>RNG30</ContRange>
> <Number>0</Number>
> <ContStatus>6</ContStatus>
> </ContainerMatrix>
> <ContainerMatrix>
> <ContRange>RNG40</ContRange>
> <Number>0</Number>
> <ContStatus>6</ContStatus>
> </ContainerMatrix>
> </ContainerMatrixes>';
> SELECT T.ContRange.query('.')
> FROM @.x.nodes('ContainerMatrixes/ContainerMatrix/ContRange') AS
> T(ContRange);
> Result is
> <ContRange>RNG20</ContRange>
> <ContRange>RNG30</ContRange>
> <ContRange>RNG40</ContRange>
Thanks for this, it is very helpfull. Studying the BOL I found the way to
use this with a table as well.
For the moment it leaves me with (another) question:
How can I tranform the XML result I get with this towards the value?
TIA,
Erik
|||Found, thanks anyway.
">> Result is
> Thanks for this, it is very helpfull. Studying the BOL I found the way to
> use this with a table as well.
> For the moment it leaves me with (another) question:
> How can I tranform the XML result I get with this towards the value?
> TIA,
> Erik
|||The query below would look like:
SELECT ID, Message.value('(/ERINOT/MessageId/@.MessageType/text())[1]',
'varchar(40)') as MType
FROM ERINOT
Best regards
Michael
"E!k Visser" <burod004@.planet.nl> wrote in message
news:5dqj01F33sav3U1@.mid.individual.net...
> Michael,
> Thanks for your contribution, but I do not understand what would be the
> difference.
> What would the query look like in you scenario?
> Perhaps good to know I might be limited by the fact I have to use ADO to
> access the database.
> Erik
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> schreef in bericht
> news:uexsXRpsHHA.412@.TK2MSFTNGP04.phx.gbl...
>
|||Thanks!
Had to like twice but now I see the difference.
regards,
Erik
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> schreef in bericht
news:%23IejCQEtHHA.3400@.TK2MSFTNGP03.phx.gbl...
> The query below would look like:
> SELECT ID, Message.value('(/ERINOT/MessageId/@.MessageType/text())[1]',
> 'varchar(40)') as MType
> FROM ERINOT
> Best regards
> Michael
> "E!k Visser" <burod004@.planet.nl> wrote in message
> news:5dqj01F33sav3U1@.mid.individual.net...
>

How to query XML

This is the content of one of the rows of the Message column.
<ERINOT VersionMajor="1" VersionMinor="1"
xmlns:mstns="http://tempuri.org/Dataset1.xsd">
<MessageId>
<SenderId>900013581</SenderId>
<ReceiverId>900013574</ReceiverId>
<GenerationDateTime>2005-09-15T05:54:01</GenerationDateTime>
<MessageType>PAS</MessageType>
<MessageNo>22412022</MessageNo>
<MessageFunction>9</MessageFunction>
</MessageId>
</ERINOT>
How can I query let's say the MessageType?
I tried this:
SET ARITHABORT ON
SELECT ID, Message.value('(/ERINOT/MessageId/@.MessageType)[0]',
'varchar(40)') as MType
FROM ERINOT
But this does not work
Any suggestion is higly appreciated,
ErikE!k Visser wrote:
> This is the content of one of the rows of the Message column.
> <ERINOT VersionMajor="1" VersionMinor="1"
> xmlns:mstns="http://tempuri.org/Dataset1.xsd">
> <MessageId>
> <SenderId>900013581</SenderId>
> <ReceiverId>900013574</ReceiverId>
> <GenerationDateTime>2005-09-15T05:54:01</GenerationDateTime>
> <MessageType>PAS</MessageType>
> <MessageNo>22412022</MessageNo>
> <MessageFunction>9</MessageFunction>
> </MessageId>
> </ERINOT>
> How can I query let's say the MessageType?
> I tried this:
> SET ARITHABORT ON
> SELECT ID, Message.value('(/ERINOT/MessageId/@.MessageType)[0]',
> 'varchar(40)') as MType
> FROM ERINOT
@.MessageType selects an attribute while you have a MessageType element.
Furthermore with XPath/XQuery the first node has index 1 not 0 so use
(/ERINOT/MessageId/MessageType)[1]
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/|||Or if your XML is not constrained by a schema, the following is more
efficient by directly accessing the text node:
(/ERINOT/MessageId/MessageType/text())[1]
Best regards
Michael
"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:ObjdJ2osHHA.2164@.TK2MSFTNGP02.phx.gbl...
> E!k Visser wrote:
> @.MessageType selects an attribute while you have a MessageType element.
> Furthermore with XPath/XQuery the first node has index 1 not 0 so use
> (/ERINOT/MessageId/MessageType)[1]
>
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/|||Michael,
Thanks for your contribution, but I do not understand what would be the
difference.
What would the query look like in you scenario?
Perhaps good to know I might be limited by the fact I have to use ADO to
access the database.
Erik
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> schreef in bericht
news:uexsXRpsHHA.412@.TK2MSFTNGP04.phx.gbl...
> Or if your XML is not constrained by a schema, the following is more
> efficient by directly accessing the text node:
> (/ERINOT/MessageId/MessageType/text())[1]
> Best regards
> Michael
> "Martin Honnen" <mahotrash@.yahoo.de> wrote in message
> news:ObjdJ2osHHA.2164@.TK2MSFTNGP02.phx.gbl...
>|||Martin,
"Martin Honnen" <mahotrash@.yahoo.de> schreef in bericht
news:ObjdJ2osHHA.2164@.TK2MSFTNGP02.phx.gbl...
> E!k Visser wrote:
> @.MessageType selects an attribute while you have a MessageType element.
> Furthermore with XPath/XQuery the first node has index 1 not 0 so use
> (/ERINOT/MessageId/MessageType)[1]
Thank you very much! This works like a charm.
But no I have another question, how can I list all nodes?
This could be the XML:
<ContainerMatrixes>
<ContainerMatrix>
<ContRange>RNG20</ContRange>
<Number>0</Number>
<ContStatus>6</ContStatus>
</ContainerMatrix>
<ContainerMatrix>
<ContRange>RNG30</ContRange>
<Number>0</Number>
<ContStatus>6</ContStatus>
</ContainerMatrix>
<ContainerMatrix>
<ContRange>RNG40</ContRange>
<Number>0</Number>
<ContStatus>6</ContStatus>
</ContainerMatrix>
</ContainerMatrixes>
And now I Like to query the ContRange and have three rows as result. It
should be as generic as possible, as in this case a ship usually carries
more than three containers...
Any suggestions?
Erik|||E!k Visser wrote:

> But no I have another question, how can I list all nodes?
> This could be the XML:
> <ContainerMatrixes>
> <ContainerMatrix>
> <ContRange>RNG20</ContRange>
> <Number>0</Number>
> <ContStatus>6</ContStatus>
> </ContainerMatrix>
> <ContainerMatrix>
> <ContRange>RNG30</ContRange>
> <Number>0</Number>
> <ContStatus>6</ContStatus>
> </ContainerMatrix>
> <ContainerMatrix>
> <ContRange>RNG40</ContRange>
> <Number>0</Number>
> <ContStatus>6</ContStatus>
> </ContainerMatrix>
> </ContainerMatrixes>
> And now I Like to query the ContRange and have three rows as result.
Here is an example, use the nodes method to shred the XML into rows,
then use one of the methods query or value to extract values.
DECLARE @.x XML;
SET @.x = ' <ContainerMatrixes>
<ContainerMatrix>
<ContRange>RNG20</ContRange>
<Number>0</Number>
<ContStatus>6</ContStatus>
</ContainerMatrix>
<ContainerMatrix>
<ContRange>RNG30</ContRange>
<Number>0</Number>
<ContStatus>6</ContStatus>
</ContainerMatrix>
<ContainerMatrix>
<ContRange>RNG40</ContRange>
<Number>0</Number>
<ContStatus>6</ContStatus>
</ContainerMatrix>
</ContainerMatrixes>';
SELECT T.ContRange.query('.')
FROM @.x.nodes('ContainerMatrixes/ContainerMatrix/ContRange') AS
T(ContRange);
Result is
<ContRange>RNG20</ContRange>
<ContRange>RNG30</ContRange>
<ContRange>RNG40</ContRange>
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/|||Martin,

> Here is an example, use the nodes method to shred the XML into rows, then
> use one of the methods query or value to extract values.
> DECLARE @.x XML;
> SET @.x = ' <ContainerMatrixes>
> <ContainerMatrix>
> <ContRange>RNG20</ContRange>
> <Number>0</Number>
> <ContStatus>6</ContStatus>
> </ContainerMatrix>
> <ContainerMatrix>
> <ContRange>RNG30</ContRange>
> <Number>0</Number>
> <ContStatus>6</ContStatus>
> </ContainerMatrix>
> <ContainerMatrix>
> <ContRange>RNG40</ContRange>
> <Number>0</Number>
> <ContStatus>6</ContStatus>
> </ContainerMatrix>
> </ContainerMatrixes>';
> SELECT T.ContRange.query('.')
> FROM @.x.nodes('ContainerMatrixes/ContainerMatrix/ContRange') AS
> T(ContRange);
> Result is
> <ContRange>RNG20</ContRange>
> <ContRange>RNG30</ContRange>
> <ContRange>RNG40</ContRange>
Thanks for this, it is very helpfull. Studying the BOL I found the way to
use this with a table as well.
For the moment it leaves me with (another) question:
How can I tranform the XML result I get with this towards the value?
TIA,
Erik|||Found, thanks anyway.
">> Result is
> Thanks for this, it is very helpfull. Studying the BOL I found the way to
> use this with a table as well.
> For the moment it leaves me with (another) question:
> How can I tranform the XML result I get with this towards the value?
> TIA,
> Erik|||The query below would look like:
SELECT ID, Message.value('(/ERINOT/MessageId/@.MessageType/text())[1]',
'varchar(40)') as MType
FROM ERINOT
Best regards
Michael
"E!k Visser" <burod004@.planet.nl> wrote in message
news:5dqj01F33sav3U1@.mid.individual.net...
> Michael,
> Thanks for your contribution, but I do not understand what would be the
> difference.
> What would the query look like in you scenario?
> Perhaps good to know I might be limited by the fact I have to use ADO to
> access the database.
> Erik
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> schreef in bericht
> news:uexsXRpsHHA.412@.TK2MSFTNGP04.phx.gbl...
>|||Thanks!
Had to like twice but now I see the difference.
regards,
Erik
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> schreef in bericht
news:%23IejCQEtHHA.3400@.TK2MSFTNGP03.phx.gbl...
> The query below would look like:
> SELECT ID, Message.value('(/ERINOT/MessageId/@.MessageType/text())[1]',
> 'varchar(40)') as MType
> FROM ERINOT
> Best regards
> Michael
> "E!k Visser" <burod004@.planet.nl> wrote in message
> news:5dqj01F33sav3U1@.mid.individual.net...
>

How to query data type of XML?

I want to query XML datas.

I have one table T.this T has two columns,id int , datasXML xml.

the datasXML has some XML datas, there into one data is:

<root>
<item val="true"></item>
<others ... />
</root>

Now,I want to query this table's datasXML column,and root/item value is true.

How to design this SQL string?

SELECT * FROM T WHERE datasXML = "<root>.." -- or other?

Please give me some samples.thanks!

Ray Lynn

In SQL2005, XQuery is great for this. There are several good articles on MSDN, for example:

http://msdn.microsoft.com/library/en-us/dnsql90/html/sql2k5_xqueryintro.asp?frame=true

|||Thank you very much!!!!!!!!!!!!!!