Retrieving multiple XML element values/ attributes with associated SQL column

Question

Retrieving multiple XML element values/ attributes with associated SQL column

DavidZahner

Ten Centuries

Points: 1169
More actions
June 8, 2006 at 4:53 pm

#168554

So I am sure that there is a simple answer to this, but I have not been able to find an example anywhere to point me in the right direction.
I have an xml column with an entire xml doc in each record and approximately 8K records in the table. Within this xml doc, there is a node with multiple distinct elements/ attributes that I want returned along with (joined to) the id entry of the actual table column. For example:
Table_1 (TabID int, XData XML)

Example row:
TabID             XData
-----------------------
12345              <Parent>
                        <Text id="Attribute_1" IdRef="Attribute_1">Some Text</Text>
                        <Text id="Attribute_2" IdRef="Attribute_2">Some Text 1</Text>
                        <Text id="Attribute_3" IdRef="Attribute_3">Some Text 2</Text>
                        <Text id="Attribute_4" IdRef="Attribute_4">Some Text 3</Text>
</Parent>

Results that I am looking for:

TabID              AttVall             ElVall
--------------------------------------
12345              Attribute_1       Some Text
12345              Attribute_2       Some Text 1
12345              Attribute_3       Some Text 2
12345              Attribute_4       Some Text 3
12346              Attribute_1       Some Text
…… For the entire table

I have tried several different functions, including
SET ANSI_PADDING ON
GO
Declare @rt xml
Declare @X xml
DECLARE @idoc int
Set @rt =
(
SELECT top 100
TabID,
XData.query('
/Parent/Text[@id]
')
As TextElement
From Table_1
For XML Auto, Type
)
--Select @rt
Select @X = (Select @rt.query('<MyRoot> { data (/) } </MyRoot>' ))
Select @X
EXEC sp_xml_preparedocument @idoc OUTPUT, @X
Select * From OpenXML(@idoc, '/Root/Parent', 8)
With (TabID int, IdRef varchar(20), Text varchar(20))

The Set @rt statement will format the output correctly to use openxml, but it needs to have only one root element, which is why I went with the attempt to add the <MyRoot> tags, but that removes the formatting performed by the first Xquery.

I am almost positive that there is an easy answer to getting the data in the right format, but can’t find any examples of retrieving xml stored in such a way.

Any help is greatly appreciated.

Viewing 3 posts - 1 through 2 (of 2 total)

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

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 1

Time for some overkill with the new stuff I mixed some XQuery with an UNPIVOT

-- Prep the sample dataDECLARE @Table_1 table (TabID int NOT NULL, XData XML) 
INSERT @Table_1(TabID, XData)SELECT 12345,        CONVERT(xml, '<Parent>
                       <Text id="Attribute_1" IdRef="Attribute_1">Some Text</Text>
                       <Text id="Attribute_2" IdRef="Attribute_2">Some Text 1</Text> 
                       <Text id="Attribute_3" IdRef="Attribute_3">Some Text 2</Text>
                       <Text id="Attribute_4" IdRef="Attribute_4">Some Text 3</Text> 
                     </Parent>') 
   UNION ALL SELECT 12346, 
        CONVERT(xml, '<Parent>
                        <Text id="Attribute_1" IdRef="Attribute_1">Some More Text</Text>
                        <Text id="Attribute_2" IdRef="Attribute_2">Some More Text 1</Text> 
                        <Text id="Attribute_3" IdRef="Attribute_3">Some More Text 2</Text>
                        <Text id="Attribute_4" IdRef="Attribute_4">Some More Text 3</Text> 
                      </Parent>') 
 
-- Now haul out the data: turn the attributes into columns (inner query), then UNPIVOT the resultsSELECT TabID, AttVal1, ElVal1  FROM (
        -- This inner query builds a table with each attribute as a column
        SELECT TabID, 
               XData.query('string((/Parent/Text[@id="Attribute_1"])[1])') AS Attribute_1,
               XData.query('string((/Parent/Text[@id="Attribute_2"])[1])') AS Attribute_2,
               XData.query('string((/Parent/Text[@id="Attribute_3"])[1])') AS Attribute_3,
               XData.query('string((/Parent/Text[@id="Attribute_4"])[1])') AS Attribute_4
          FROM @table_1
        ) t1
     -- The outer query then UNPIVOTs all the attribute columns into two columns
     -- one column for the original column name ("Attribute_1", etc.) and one column for the value ("Some Text")
     UNPIVOT 
        (
          ElVal1 FOR AttVal1 IN (Attribute_1, Attribute_2, Attribute_3, Attribute_4)
         ) AS res
 
Results:
TabID       AttVal1         ElVal1
----------- --------------- --------------------
12345       Attribute_1     Some Text 
12345       Attribute_2     Some Text 1 
12345       Attribute_3     Some Text 2 
12345       Attribute_4     Some Text 3 
12346       Attribute_1     Some More Text 
12346       Attribute_2     Some More Text 1 
12346       Attribute_3     Some More Text 2 
12346       Attribute_4     Some More Text 3

-Eddie

Eddie Wuerch
MCM: SQL

DavidZahner Ten Centuries Points: 1169 More actions · Answer 2

Very creative with the Unpivot, worked like a charm.

Thanks for the assist!

--Dave Z.

David Zahner

MCTS: SQL Server 2005

MCP

CCNA

A+