XML Workshop 26 – SELECT * FROM XML

  • 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,

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

    .

  • 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)

  • 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.

  • 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

    .

  • Thank you, a very useful article.

  • is XMLTable() function a microsoft SQL server function

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

  • 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.

    .

  • where i can find XMLTable() function

    is it microsoft sql server ?

    regards

    majd

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

    .

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

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

  • 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

  • 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

  • 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.

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

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