How to get the name of XML elements with TSQL?

Question

Post reply

How to get the name of XML elements with TSQL?

Paul-773042

Old Hand

Points: 351
More actions
June 7, 2008 at 8:16 am

#129391

I have an application that is using XML from a 3rd party. I don't always know what XML elements will be present, but I need to capture all of them and index them in a SQL Server 2005 database. I have created a simple example of my problem here. Consider the following TSQL fragment:
Download TSQL Example Here
What I need is a query that will return both the name of the elements within the "person" element and the values. I would like the query to return a table with two columns, the first column containing the name of the xml element/node and the second column containing the value of that element/node.
I know how I could write this query if I know the element names beforehand, its simple:
----------------------------------------------------------------
selectx.value('first_name[1]', 'varchar(128)') as 'first_name'
from@xml.nodes('/person') x(x)
----------------------------------------------------------------
Thats fine if I know the names of the elements, but in my case I don't. This is a user generated metadata system, so I have no way of knowing what the element names might be.
Hopefully there is a TSQL guru out there that can help. Thanks!
- Paul

Viewing 9 posts - 1 through 8 (of 8 total)

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

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 1

Ken Simmons

SSCertifiable

Points: 7822

June 7, 2008 at 9:33 am

#825817

See attached...

Paul-773042 Old Hand Points: 351 More actions · Answer 2

Sweet!

Ken, you are the Man!

That's exactly what I was trying to do. Thank you very much.

Now I am trying to apply the solution to my real problem. Here are the details.

The source XML can bee viewed here:

http://www.google.com/base/feeds/snippets?bq=portland

This is an ATOM feed. For each /feed/entry there are custom attributes using the "g" namespace. These are the values I am interested in. I need to know the name of the node, the "@type" attribute, and the value.

I know how to get the values from the XML if I know the names of the nodes beforehand, here is a TSQL example:

declare@xml xml, -- used to store the xml from the 3rd party.

@url varchar(800) -- the url where the xml is downloaded from.

select@url = 'http://www.google.com/base/feeds/snippets?bq=portland', -- set the URL to 3rd party's download location.

@xml = dbo.getXmlFromUrl(@url) -- this function grabs the xml from the provided URL

-- show the original xml

select@xml as 'xml in its original form from 3rd party'

-- table based view of xml attributes (using some random attribute names for example)

;withxmlnamespaces

(

default 'http://www.w3.org/2005/Atom',

'http://base.google.com/ns/1.0' as g

)

selectreplace(x.value('id[1]', 'varchar(max)'), 'http://www.google.com/base/feeds/snippets/', '') baseId,

x.value('g:bathrooms[1]', 'varchar(max)') bathrooms,

x.value('g:image_link[1]', 'varchar(max)') image_link,

x.value('g:expiration_date[1]', 'varchar(max)') expiration_date,

x.value('g:phone[1]', 'varchar(max)') phone

from@xml.nodes('feed/entry') x(x)

Notice there is a UDF called getXmlFromUrl, this just downloads the XML in the link above.

So ideally I would have a query that provides all these "custom attribute" names, types, and values from the "g" namespace.

I tried the approach you provided, but it really didn't work out on this XML. I think it has something to do with the custom namespaces? Any thoughts, insights, ideas?

Thanks again. - Paul

Paul-773042 Old Hand Points: 351 More actions · Answer 3

I have been working on this problem and have a something that gives me what I want, but it takes 7 seconds to process. Any ideas why this is so slow?

Here is the TSQL code

I had to use top 1, else it was returning too much stuff... thats probably part of the problem.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 4

If you know the deepest level is three, you can use something like this

DECLARE@r XML

SET@r = '

<Reference>

<Basic>

<Book>A1.Hj1.JU9</Book>

</Basic>

<App>

<A>AK9.HL9.J0</A>

<A>A18.H.PJ69</A>

</App>

B13.H98.P9

B18.HO9.JIU8

<DI>

<D>D23.HYT.P6R</D>

</DI>

</Reference>

'

SELECTt.c.value('local-name(..)', 'varchar(max)') AS ParentNodeName,

t.c.value('local-name(.)', 'varchar(max)') AS NodeName,

t.c.value('text()[1]', 'varchar(max)') AS NodeText

FROM@r.nodes('/*/*/*') AS t(c)

N 56°04'39.16"
E 12°55'05.25"

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

Peter - your XML got whacked by the SSC markup. If you replace the left and right brackets with their HTML version - it will show up again...

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

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 6

Thanks!

If was indeed the < character mocking up.

Happy New Year everyone.

N 56°04'39.16"
E 12°55'05.25"

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

Paul (6/7/2008)
I have been working on this problem and have a something that gives me what I want, but it takes 7 seconds to process. Any ideas why this is so slow?
Here is the TSQL code

I cannot access your link.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

agoutham-1077550 SSC Journeyman Points: 99 More actions · Answer 8

Replying to Ken Simmons post:

Thanks for posting your code. I have used your code in a Stored Procedure. One thing that I 'm not able to figure out is how to retrieve attribute name/value from the each of the nodes. Any help will be greatly appreciated. I'm new to Xquery. Thanks.