Convert XML column into SQL Table 1

[alexjones]

This is my first exposure to XML.

I have a SQL Server 2005 Payments database with an XML column that looks like:

<entities>
<entity id="4191" name="Center for Success, Inc.">
<elements>
<element name="YTDDue">242041.85</element>
<element name="YTDPaid">112049.36</element>
</elements>
</entity>
<entity id="4201" name="Pine Forest Association, Inc.">
<elements>
<element name="YTDDue">44191.12</element>
<element name="YTDPaid">32071.78</element>
</elements>
</entity>
</entities>

In the interests of simplicity, I have displayed two elements. In real life there are 24.

I want to use this to populate a table that looks like:

ID Name YTDDue YTDPaid
4191 Center for Success, Inc 242041.85 112049.36
4201 Pine Forest Association, Inc 44191.12 32071.78

The following code:

if object_id('tempdb.dbo.#XmlSourceTable') is not null drop table #XmlSourceTable

CREATE TABLE #XmlSourceTable (
   RecordId int identity(1,1) not null primary key,
   XmlData xml not null
)
 
DECLARE @xml xml
SET @xml = 
'<entities>
  <entity id="4191" name="Center for Success, Inc.">
    <elements>
      <element name="YTDDue">242041.85</element>
      <element name="YTDPaid">112049.36</element>
    </elements>
  </entity>
  <entity id="4201" name="Pine Forest Association, Inc.">
    <elements>
      <element name="YTDDue">44191.12</element>
      <element name="YTDPaid">32071.78</element>
    </elements>
  </entity>
</entities> '
              
INSERT INTO #XmlSourceTable(XmlData)
       VALUES(@xml)
       
SELECT 
       tbl.col.value('@id', 'int') as EntityId,
       tbl.col.value('@name', 'nvarchar(80)') as EntityName,
       tbl.col.query('elements') as EntityElements
  FROM #XmlSourceTable
  CROSS APPLY XmlData.nodes('/entities/entity') as tbl(col)

gets me the first two columns and an XML snippet containing the rest of my data.

I haven't a clue how to extract the remaining columns from a snippet where the tags are all the same and I have to grab the values based on the name attribute.

Any help here would be greatly appreciated.

 

[gmmastros]

I'm no XML expert, so there may be a better way than this:

Select EntityId, 
       EntityName, 
       Min(Case When Name='YTDDue' Then [Value] End) As YTDDue,
       Min(Case When Name='YTDPaid' Then [Value] End) As YTDPaid
From   (
SELECT
       tbl.col.value('../../@id', 'int') as EntityId,
       tbl.col.value('../../@name', 'nvarchar(80)') as EntityName,
       tbl.col.value('@name', 'nvarchar(80)') As Name,
       tbl.col.value('.','Decimal(10,2)') As [Value]
  FROM #XmlSourceTable
  CROSS APPLY XmlData.nodes('/entities/entity/elements/element') as tbl(col)
       ) As A
Group By EntityId, EntityName

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom