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