Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Flatten XML into SQL Server Table 1

Status
Not open for further replies.

alexjones

Programmer
Jul 27, 2001
132
US
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:
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. [banghead]

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.
 
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.
 
Also the data type smallmoney is just suggestive. Use money or else if the monetary value can be much bigger.
 
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?
 
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]
 
That seems to have done the trick! Thank you so much. Have a star [smile]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top