XML Workshop 26 – SELECT * FROM XML

Question

XML Workshop 26 – SELECT * FROM XML

Viewing 15 posts - 16 through 30 (of 31 total)

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

Lawrence Moore Ten Centuries Points: 1203 More actions · Answer 1

Revised XPath query that would work for above namespace is:

SELECT @x.value(' declare namespace NS="http://www.mycomp.com/lbg.paris.load.bulkdealcomponentinsert.xsd"; /NS:BulkDealComponentInsert[1]/NS:Deal[1]/NS:OrgCode[1]', 'varchar(100)') AS OrgCode

Hope this helps,

Cheers,

jacob sebastian SSChampion Points: 11822 More actions · Answer 2

Yes, the next item in the task list is to support namespaces.

.

thomas.briscoe SSC-Addicted Points: 488 More actions · Answer 3

Thank you so very much for this article and the link to the source code for the function XMLTable. I am definitely making use of it.

I am querying the Report Server for reports to look at each report. I am looking at each of their DataSource and in particular the DataSet and its Query.

My task is to list all the tables and views used by these reports. As reports are continually being revised, added, and removed, I am creating code that will dynamically look at the report XML. Find the Query for each and look in each Query for the names of those tables and views used.

Thank you for the NameSpace tip as well.

I am still working on expanding the code that I need, but I have my version to get the Name of the DataSet.

SELECT @x.value(' declare namespace NS="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"; /NS:Report[1]/NS:DataSets[1]/NS:DataSet[1]/@Name','VARCHAR(50)') AS 'DataSetName'

(58.30115757480578, -134.4143772125244)

jwiner SSCrazy Points: 2241 More actions · Answer 4

Very cool idea. Just wondering if you've 'reinvented the wheel' a bit. If you use OPENXML and omit the WITH clause, it produces an edge table with very similar results.

jacob sebastian SSChampion Points: 11822 More actions · Answer 5

Well, this may be considered as a 'better wheel' because the other 'wheel' does not turn well. If you closely observe the results produced by this function and compare it with the output of OPENXML() you can see a number of differences.

In addition, OPENXML() can process only one XML document at a time. If you want to process all XML documents stored in an XML column, you will need a WHILE LOOP. You need a 3 step process to call OPENXML() - prepare, query, clean. OPENXML() cannot be used within functions. if you forget to call the clean up procedure, you might end up with memory leaks. OPENXML() also allocates (actually the call to sp_xml_preparedocument) 1/8 of the total server memory for XML cache.

The function being discussed here, uses XQuery and a recursive CTE to process all the elements and attributes of the given XML document. It can be used in a SET based query. You dont need a loop to process all the XML documents stored in an XML column. The additional columns such as XPath, ParentNode etc allows to quickly query the pieces that one is looking for.

I do not intend to claim that this is better than any other approaches. This is just one way of querying XML documents and comes with its own set of PROS and CONS. Just like any other method or approach we use in our programming tasks, if this is found to be helpful in a given scenario let us use it.

Thanks

.

ramlink_rv Old Hand Points: 358 More actions · Answer 6

ramlink_rv

Old Hand

Points: 358

July 1, 2010 at 4:13 am

#1187547

Thank you, a very useful article.

majd84 Grasshopper Points: 21 More actions · Answer 7

is XMLTable() function a microsoft SQL server function

i couldn't find it and the parser didn't recognize it (sql2005 and sql 2008)

jacob sebastian SSChampion Points: 11822 More actions · Answer 8

No, this is not an in-built function. The article had a link to download the source of this function.

You can find it here.

.

majd84 Grasshopper Points: 21 More actions · Answer 9

where i can find XMLTable() function

is it microsoft sql server ?

regards

majd

jacob sebastian SSChampion Points: 11822 More actions · Answer 10

Look at the link given in my previous reply. That link will take you to the page where the function is listed.

.

anthonyqkiernan SSC Rookie Points: 46 More actions · Answer 11

Excellent article. But, how do you do it from - say - the twitter feed?

Pagan DBA Right there with Babe Points: 794 More actions · Answer 12

I need the XMLTable function desperately. I tried downloading it from the link provided @beyondrelational. For past 2 days the site seems to be down. Is it possible to post the implementation here?

drew.allen SSC Guru Points: 76989 More actions · Answer 13

Pagan DBA - Wednesday, September 12, 2018 1:54 PM
I need the XMLTable function desperately. I tried downloading it from the link provided @beyondrelational. For past 2 days the site seems to be down. Is it possible to post the implementation here?

The author of this article was last active five years ago. It's unlikely that he will be back to see this request.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Sue_H SSC Guru Points: 90860 More actions · Answer 14

Pagan DBA - Wednesday, September 12, 2018 1:54 PM
I need the XMLTable function desperately. I tried downloading it from the link provided @beyondrelational. For past 2 days the site seems to be down. Is it possible to post the implementation here?

You can find his code for that function the following article:
SELECT * FROM XML

Sue

Pagan DBA Right there with Babe Points: 794 More actions · Answer 15

Sue_H - Wednesday, September 12, 2018 2:38 PM
Pagan DBA - Wednesday, September 12, 2018 1:54 PM
I need the XMLTable function desperately. I tried downloading it from the link provided @beyondrelational. For past 2 days the site seems to be down. Is it possible to post the implementation here?
You can find his code for that function the following article:
SELECT * FROM XML

Sue

Thank you! Hopefully it is the latest version. It does say does not support namespaces. Not sure if that support was ever added. If some kind soul is reading this and knows the latest and greatest version, please help. For now, I will try to work off what is there on author website.