how to loop through xml node in SQL server

I have a table (A), which has one field (outerxml), data type is xml. I can use SELECT * FROM A WHERE OUTERXML.EXIST('\Country')=1 to query the records, very convenience.

Now I have a XML string (like <country>USA</country><postalcode>12345</postalcode>), I don't know which tag name could appear in the XML string. what's best way to get tag name (country or postalcode, not value) in a XML string?
If I can loop through xmlNode, that's would be great. BTW, I don't want to use string function to parse it.

I need to do it inside SQL server 2005 stored procedure. thanks.
LVL 4
Richard_XPAsked:
Who is Participating?
 
wdosanjosConnect With a Mentor Commented:
Try this out:  (the xml must be within a root tag)

DECLARE 
    @idoc INT,  
    @xml XML  
    
SELECT @xml = '<root><country>USA</country><postalcode>12345</postalcode></root>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @xml  

SELECT localname FROM OPENXML(@idoc,'/',3) 
 WHERE localname not in ('root', '#text')

EXEC sp_xml_removedocument @idoc 

/*
OUTPUT:

localname
---------
country
postalcode

*/

Open in new window


A more sophisticated version is found here:
http://beyondrelational.com/blogs/jacob/archive/2009/01/23/xquery-lab-39-retrieve-fully-qualified-path-of-attributes-and-elements.aspx
0
 
Aaron ShiloChief Database ArchitectCommented:
look at this example

DECLARE @x XML

SET @x = 

'<Employees>

  <Employee ID="101">

    <Name>Jacob</Name>

    <Department>IT</Department>

  </Employee>

  <Employee ID="354">

    <Name>Steve</Name>

    <Department>IT</Department>

  </Employee>

  <Employee ID="456">

    <Name>Bob</Name>

    <Department>IT</Department>

  </Employee>

  <Employee ID="478">

    <Name>Joe</Name>

    <Department>IT</Department>

  </Employee>

  <Employee ID="981">

    <Name>Louis</Name>

    <Department>IT</Department>

  </Employee>

</Employees>'

 

-- Total count of <Employee> Nodes

DECLARE @max INT, @i INT

SELECT 

    @max = @x.query('<e>

                        { count(/Employees/Employee) }

                    </e>').value('e[1]','int') 

 

-- Set counter variable to 1

SET @i = 1

 

-- variable to store employee name

DECLARE @EmpName VARCHAR(10)

 

-- loop starts

WHILE @i <= @max BEGIN

    -- select "Name" to the variable

    SELECT

        @EmpName = x.value('Name[1]', 'VARCHAR(20)') 

    FROM 

    @x.nodes('/Employees/Employee[position()=sql:variable("@i")]') 

    e(x)

 

    -- print the name

    PRINT @EmpName

 

    -- increment counter

    SET @i = @i + 1        

END
 

Open in new window


0
 
Richard_XPAuthor Commented:
sorry, doesn't help. I don't know element name (tag name) in xml string, so I can not use xml function like nodes, query, exists
0
 
Richard_XPAuthor Commented:
Looks good, let me try ti tomorrow. thanks.
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.