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.

• Eddie Wuerch

  SSChampion

  Points: 12540

  More actions

  June 9, 2006 at 12:06 am

  #642708

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

  -- Prep the sample data
  DECLARE @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 results
  SELECT 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

  June 9, 2006 at 9:14 am

  #642830

  Very creative with the Unpivot, worked like a charm.

  Thanks for the assist!

  --Dave Z.

  David Zahner

  MCTS: SQL Server 2005

  MCP

  CCNA

  A+