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:
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.
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.
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.