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.

How to get the name of XML elements with TSQL?

Cookies on SQLServerCentral