Can I do SELECT * in XQuery ?

Question

Post reply

Can I do SELECT * in XQuery ?

Tom Leykis

SSC Eights!

Points: 899
More actions
February 7, 2008 at 2:55 pm

#182473

With sql 2k I always used openxml, but what a memory hog....
In sql 2005, can I use xquery to take an xml variable and do a select * to insert into a table?
Example:
DECLARE @x XML
SET @x =
'<Customers>
<Customer>
<Name>Bob</Name>
<Address>123 main st.</Address>
</Customer>
<Customer>
<Name>Fred</Name>
<Address>456 main st.</Address>
</Customer>
<Customer>
<Name>Jim</Name>
<Address>789 main st.</Address>
</Customer>
</Customers>'
Is there a way in XQuery to do something like:
Select * From @X
and get all the values in column form?

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 1

in 2005 you have the xml datatype see how you can use that to help for you.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Tom Leykis SSC Eights! Points: 899 More actions · Answer 2

Yes I know that, I'm using it in my example above. But how can I display the results of the variable using XQuery, the same way I do in OpenXML? Or is this not possible?

Thanks...

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

My XQuery knowledge is unfortunately new and grossly incomplete, but from what I do know, I don't think it's possible. XPath queries are a way of navigating the XML to select a set of nodes. I think once the nodes are selected you still have to tell SQL Server what to do with them, how to define them. Every example I've seen of converting XML to columns in a result set involved defining the path of the column within the query.

I'll be interested in seeing information to the contrary though.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 4

Does this help?

Select r.value('local-name(.)','varchar(20)') as Name,

r.value('(text())[1]','varchar(100)') as Value

From @X.nodes('//*') as x(r)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Tom Leykis SSC Eights! Points: 899 More actions · Answer 5

Mark (2/8/2008)
Does this help?
Select r.value('local-name(.)','varchar(20)') as Name,
r.value('(text())[1]','varchar(100)') as Value
From @X.nodes('//*') as x(r)

Now we're cooking! Any chance that could be turned into a Horizontal table instead?

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 6

Tom Leykis (2/8/2008)
Mark (2/8/2008)
Does this help?
Select r.value('local-name(.)','varchar(20)') as Name,
r.value('(text())[1]','varchar(100)') as Value
From @X.nodes('//*') as x(r)
Now we're cooking! Any chance that could be turned into a Horizontal table instead?

No easy way that I know of other than an unpleasant pivot using dynamic SQL. Any chance you could do this client side instead?

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Tom Leykis SSC Eights! Points: 899 More actions · Answer 7

Not possible in my case. I think what I will do is just pass each xml node into tables individually and work with the data from there. Unless of course OPENXML turns out to be ok. OPENXML is at times just hammering our current 8x 4g Sql2k machine but may be ok on our new 16x 32g 2005 box. What is the difference in OpenXML vs XQuery as far as memory goes, is Open using TempDb and XQuery using Ram, or dp they use the same resources to parse the xml?

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 8

Internal to SQL Server, they peform almost identically. The main difference is that OPENXML has to instantiate an XML parser that chews up a ton of memory outside of SQL Server. XQuery is radically less costly, if a bit more verbose in its implementation.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 9

Tom -

what kind of output were you expecting?

Try this on for size:

Select r.value('./Name[1]','varchar(20)') as Name,

r.value('./Address[1]','varchar(100)') as Value

From @X.nodes('/Customers/Customer') as x(r)

I'm thinking this is more in line with what you want.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Tom Leykis SSC Eights! Points: 899 More actions · Answer 10

Tom Leykis

SSC Eights!

Points: 899

February 11, 2008 at 10:41 am

#776999

That was it alright. I really appreciate the help!

Matt Woodward Newbie Points: 2 More actions · Answer 11

I'm a total noob to xquery in SQL Server so I was very glad I found this--it's very close to what I'm trying to do as well.

I was also wondering if it's possible to get a horizontal table as opposed to name/value pairs, and the last post for some reason returns nulls in my XML structure, which for the purposes of this discussion is identical to the sample provided by the original poster.

So instead of this:

name value

------------------------------

node NULL

subNode1 sn1Value1

subNode2 sn2Value1

node NULL

subNode1 sn1Value2

subNode2 sn2Value2

Is it possible to get this?

subNode1 subNode2

-----------------------------------

sn1Value1 sn2Value1

sn1Value2 sn2Value2

Any ideas would be greatly appreciated. Thanks!

Matt

Matt Woodward Newbie Points: 2 More actions · Answer 12

Never mind--got it sorted out. Glad I came across these posts!

ganeshmuthuvelu SSCrazy Points: 2177 More actions · Answer 13

Any way to get the attribute values also in the same way.. This is my XML

</ROWS>

and

Select r.value('local-name(.)','varchar(20)') as Name,

r.value('(text())[1]','varchar(100)') as Value

From @doc2.nodes('//*') as x(r)

does not provide the attribute values..

Thanks,

Ganesh