none
Return only root tag of a document RRS feed

  • Question

  • For once it is time for me to ask a question. Say that I have an XML document like this:

    DECLARE @x xml = '<root attr1="1" attr2="snyltare" attr3="Blockchoklad">
       <element1>
           <ele1 ele1attr1="nisse"/>
           <ele1 ele1attr1="kalle"/>
           <ele1 ele1attr1="petra"/>
           <ele1 ele1attr1="pekka"/>
           <ele1 ele1attr1="stina"/>
       </element1>
       <element2>
           <ele2 ele2attr1="nisse"/>
           <ele2 ele2attr1="kalle"/>
           <ele2 ele2attr1="petra"/>
           <ele2 ele2attr1="pekka"/>
           <ele2 ele2attr1="stina"/>
       </element2>
    </root>'

    I want to return the root tag with its attribute only, but without the elements, and I want to do this efficiently. Thus, I don't believe in:

    SET @x.modify('delete /root/*')
    SELECT @x

    The reason for this is a database design I don't really agree with, and where they have shoved to much into the same XML document. One function wants to display a summary of all records for a project, and that summary only needs information from the root attributes. There are quite a few attributes, and they could get the idea to add a new one, so I don't fancy the idea of unpacking each one explicitly with .value to relational format and then FOR XML back to XML.

    Since the nested elements logically are part of root tag, this seems like a wrong thing to do, but some of you knows Xquery better than I do, so I could just as well ask.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, October 15, 2019 7:41 PM

Answers

  • Hi Erland,

    (1) Here is one of the available ways to get just the root and its attributes:

    DECLARE @x xml = '<root attr1="1" attr2="snyltare" attr3="Blockchoklad">
       <element1>
           <ele1 ele1attr1="nisse"/>
           <ele1 ele1attr1="kalle"/>
           <ele1 ele1attr1="petra"/>
           <ele1 ele1attr1="pekka"/>
           <ele1 ele1attr1="stina"/>
       </element1>
       <element2>
           <ele2 ele2attr1="nisse"/>
           <ele2 ele2attr1="kalle"/>
           <ele2 ele2attr1="petra"/>
           <ele2 ele2attr1="pekka"/>
           <ele2 ele2attr1="stina"/>
       </element2>
    </root>';
    
    SELECT @x.query('<root>
    {
     for $i in /root/@*
       return $i
    }
    </root>') AS justRoot;

    (2) Here is how to query it in a generic way, and return a relational structure:

    DECLARE @x xml = '<root attr1="1" attr2="snyltare" attr3="Blockchoklad">
       <element1>
           <ele1 ele1attr1="nisse"/>
           <ele1 ele1attr1="kalle"/>
           <ele1 ele1attr1="petra"/>
           <ele1 ele1attr1="pekka"/>
           <ele1 ele1attr1="stina"/>
       </element1>
       <element2>
           <ele2 ele2attr1="nisse"/>
           <ele2 ele2attr1="kalle"/>
           <ele2 ele2attr1="petra"/>
           <ele2 ele2attr1="pekka"/>
           <ele2 ele2attr1="stina"/>
       </element2>
    </root>';
    
    DECLARE @xml XML = 
    	(SELECT @x.query('<root>
    	{
    		for $i in /root/@*
    		return <r>{$i}</r>
    	}
    	</root>'));
    
    SELECT col.value('local-name((@*)[1])', 'varchar(30)') AS attrName
    	, col.value('(@*)[1]','varchar(100)') AS attrValue
    FROM @xml.nodes('/root/r') AS tab(col);



    Wednesday, October 16, 2019 11:42 AM

All replies

  • Hi Erland,

    (1) Here is one of the available ways to get just the root and its attributes:

    DECLARE @x xml = '<root attr1="1" attr2="snyltare" attr3="Blockchoklad">
       <element1>
           <ele1 ele1attr1="nisse"/>
           <ele1 ele1attr1="kalle"/>
           <ele1 ele1attr1="petra"/>
           <ele1 ele1attr1="pekka"/>
           <ele1 ele1attr1="stina"/>
       </element1>
       <element2>
           <ele2 ele2attr1="nisse"/>
           <ele2 ele2attr1="kalle"/>
           <ele2 ele2attr1="petra"/>
           <ele2 ele2attr1="pekka"/>
           <ele2 ele2attr1="stina"/>
       </element2>
    </root>';
    
    SELECT @x.query('<root>
    {
     for $i in /root/@*
       return $i
    }
    </root>') AS justRoot;

    (2) Here is how to query it in a generic way, and return a relational structure:

    DECLARE @x xml = '<root attr1="1" attr2="snyltare" attr3="Blockchoklad">
       <element1>
           <ele1 ele1attr1="nisse"/>
           <ele1 ele1attr1="kalle"/>
           <ele1 ele1attr1="petra"/>
           <ele1 ele1attr1="pekka"/>
           <ele1 ele1attr1="stina"/>
       </element1>
       <element2>
           <ele2 ele2attr1="nisse"/>
           <ele2 ele2attr1="kalle"/>
           <ele2 ele2attr1="petra"/>
           <ele2 ele2attr1="pekka"/>
           <ele2 ele2attr1="stina"/>
       </element2>
    </root>';
    
    DECLARE @xml XML = 
    	(SELECT @x.query('<root>
    	{
    		for $i in /root/@*
    		return <r>{$i}</r>
    	}
    	</root>'));
    
    SELECT col.value('local-name((@*)[1])', 'varchar(30)') AS attrName
    	, col.value('(@*)[1]','varchar(100)') AS attrValue
    FROM @xml.nodes('/root/r') AS tab(col);



    Wednesday, October 16, 2019 11:42 AM
  • Thanks Yitzhak! The first solution was exactly what I was looking for.



    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, October 16, 2019 3:10 PM
  • Hi Erland,

    Glad to hear that the proposed solution worked for you.

    I recently asked Microsoft to beef up NoSQL functionality in SQL Server. Please check it out, add your comments, and vote for it: SQL Server vNext (post 2019) and NoSQL functionality

    Wednesday, October 16, 2019 3:28 PM