Solved

XML Nodes and show tree

Posted on 2013-06-14
2
462 Views
Last Modified: 2013-06-14
I would like a function that I pass in XML and get the "schema" or "tree" back

For example
From this xml I wouls set @XmlDoc
ss
And get this returned
ss2
0
Comment
Question by:lrbrister
2 Comments
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 39249025
Hi.

Using your example XML:
DECLARE @XmlDoc AS XML = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<org.m5.api.v1.Response xmlns:m5="http://www.m5net.com/org/m5/data/v2/cti" xmlns:csta="http://www.ecma-international.org/standards/ecma-323/csta/ed5">
    <ErrorCount>0</ErrorCount>
    <Id>7</Id>
    <Result xsi:type="org.m5.data.v2.cti.HostedConnectObject" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
        <csta:MonitorStartResponse>
            <csta:monitorCrossRefID>726</csta:monitorCrossRefID>
        </csta:MonitorStartResponse>
    </Result>
</org.m5.api.v1.Response>
';

Open in new window


You can try something like this:
- search for all child nodes '//*'
- get local-name of the current node '.'
- get local-name of the current node's parent '..'
SELECT x.n.value('fn:local-name(.)', 'varchar(255)') AS [Node]
     , x.n.value('fn:local-name(..)', 'varchar(255)') AS [ParentNode]
FROM @XmlDoc.nodes('//*') x(n)
;

Open in new window


MSDN: http://msdn.microsoft.com/en-us/library/ms188282.aspx

I hope that helps!

Best regards and happy coding,

Kevin
0
 

Author Closing Comment

by:lrbrister
ID: 39249055
Thanks.
That worked as well as EXEC sp_xml_preparedocument @handle OUTPUT, @XmlDoc
Watch for follow-up
0

Featured Post

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS SQL - Rotating Values in SQL 9 50
SQL XML ALL Nodes Compare in function 2 17
ASP.NET 5 Templates 2 65
Extract string portion 2 12
Browsing the questions asked to the Experts of this forum, you will be amazed to see how many times people are headaching about monster regular expressions (regex) to select that specific part of some HTML or XML file they want to extract. The examp…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now