Getting all nodes and node details from xml using xquery

[Shilpa]

Hi,

I haev the following code:

DECLARE @x xml
SET @x='
<Root>
<row id="1"><name>La rry</name><oflw>some text</oflw></row>
<row id="2"><name>Jo e</name></row>
<row id="3" />
</Root>
'
exec sp_xml_prepared ocument @idoc OUTPUT, @x
SELECT * FROM OPENXML(@idoc, '/Root')

This gives the following details
id
parentid
nodetype localname

prefix

namespaceuri
datatype

prev text



I want to get the same details using XQuery, please let me know how to
go about it.

Regards,
Shilpa

[Erland Sommarskog]

Re: Getting all nodes and node details from xml using xquery

Shilpa (shilpa.nagavar a@unisys.com) writes:[color=blue]
> I haev the following code:
>
> DECLARE @x xml
> SET @x='
><Root>
> <row id="1"><name>La rry</name><oflw>some text</oflw></row>
> <row id="2"><name>Jo e</name></row>
> <row id="3" />
></Root>
> '
> exec sp_xml_prepared ocument @idoc OUTPUT, @x
> SELECT * FROM OPENXML(@idoc, '/Root')
>...
> I want to get the same details using XQuery, please let me know how to
> go about it.[/color]

I am not sure that you can to this easily. You use a very special form
of OPENXML, the one without a WITH clause, that gives you an edge table.
I have never found any use for edge tables, but maybe I've overlooked
something.

Since I'm no specialist in XML, I've consulted my MVP colleagues who know
this topic better, so hopefully I will be able to return with a more
definitive answer.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Erland Sommarskog]

Re: Getting all nodes and node details from xml using xquery

"Shilpa" <shilpa.nagavar a@unisys.com> writes:[color=blue]
>I haev the following code:
>
>DECLARE @x xml
>SET @x='
><Root>
> <row id="1"><name>La rry</name><oflw>some text</oflw></row>
> <row id="2"><name>Jo e</name></row>
> <row id="3" />
></Root>
>'
>exec sp_xml_prepared ocument @idoc OUTPUT, @x
>SELECT * FROM OPENXML(@idoc, '/Root')
>
>This gives the following details
>id
>parentid
>nodetype localname
>
>prefix
>
>namespaceuri
> datatype
>
> prev text[/color]


I inquired about this, and you can only get localname, namespace URI
and the node's value with XQuery. You cannot get the positional node
information or node ids or prefixes.

For an example, look up the newsgroup microsoft.publi c.sqlserver.xml .
Michael Rys, who is the Program Manager for XML in SQL Server, and
whom I got this information from, says he recently posted an example
to that group.

--
Erland Sommarskog, Stockholm, esquel@sommarsk og.se