Flatten XML into SQL Server 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 interest of simplicity, I have displayed two entities with two elements each. In reality, there are over 700 entities with 24 elements each.

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

ID Name YTDDue TYDPaid
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.

gmmastros, on the SQL Server Programming forum suggested picking the value in a case statement on name. This worked beautifully on my 2x2 example, but took forever on the full data.

I have no authority to change the xml in any way.

Any help here would be greatly appreciated.

 

[tsuji]

Try this, if I understand what you want.
[tt]
SELECT tbl.col.value('@id', 'int') as EntityId,
tbl.col.value('@name', 'nvarchar(80)') as EntityName,
tbl.col.[blue]value[/blue]('elements[blue]/element[@name="YTDDue"]','smallmoney[/blue]') as Entity[blue]ElementYTDDue[/blue]
tbl.col.[blue]value[/blue]('elements[blue]/element[@name="YTDPaid"]','smallmoney[/blue]') as Entity[blue]ElementYTDPaid[/blue]
FROM #XmlSourceTable
CROSS APPLY XmlData.nodes('/entities/entity') as tbl(col)
[/tt]
The aliases are sure arbitrary.

 

[tsuji]

Also the data type smallmoney is just suggestive. Use money or else if the monetary value can be much bigger.

 

[alexjones]

Thank you for your quick response.

The code you suggested resulted in the error:

XQuery [#XmlSourceTable.XmlData.value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

I got the same message after replacing

tbl.col.value('elements/element[@name="YTDDue"]' ...
with
tbl.col.value('(elements/element)[@name="YTDDue"]' ...

Does the error message provide a clue?

 

[tsuji]

Sure, just apply the position() to pick one the first to ensure singleton is returned always.
[tt]
SELECT tbl.col.value('@id', 'int') as EntityId,
tbl.col.value('@name', 'nvarchar(80)') as EntityName,
tbl.col.value('[red]([/red]elements/element[@name="YTDDue"][red])[1][/red]','smallmoney') as EntityElementYTDDue
tbl.col.value('[red]([/red]elements/element[@name="YTDPaid"][red])[1][/red]','smallmoney') as EntityElementYTDPaid
FROM #XmlSourceTable
CROSS APPLY XmlData.nodes('/entities/entity') as tbl(col)
[/tt]

 

[alexjones]

That seems to have done the trick! Thank you so much. Have a star