XML Nodes and show tree

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
Larry Bristersr. DeveloperAsked:
Who is Participating?
 
Kevin CrossChief Technology OfficerCommented:
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
 
Larry Bristersr. DeveloperAuthor Commented:
Thanks.
That worked as well as EXEC sp_xml_preparedocument @handle OUTPUT, @XmlDoc
Watch for follow-up
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.