Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to shred XML

Author  Topic 

Bex
Aged Yak Warrior

580 Posts

Posted - 2013-03-18 : 09:05:58
I have a table:
CREATE TABLE [dbo].[Incident](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[IncidentNo] [varchar](20) NOT NULL,
[IncidentDate] [datetime] NULL,
[CreatedDate] [datetime] NULL,
[AssignedToName] [nvarchar](250) NULL,
[InsertedDateTime] [datetime] NULL,
[IncidentData] [xml] NULL,
CONSTRAINT [PK_Incident] PRIMARY KEY CLUSTERED
(
[ID] ASC
))


The table holds data that is created when a user fills in a form (of which there are several templates). The IncidentData column contains the whole form stored as XML. This xml is untyped, and the structure changes depending on what parts of the form the user completes. The client wishes to report on the content contained in this form. We could have up to 1 million forms completed every year, and we are to store up to 7 years of data. We need to report across the whole period. I need some method of exposing the data to the reports, in a way that is performant. An original idea was the shred the data/xml fields that are to be reported on into separate tables. However, the user will want to add additional columns to reports, and potentially report retrospectively. Therefore, everytime they wanted to do this, we would have to add a new column to the table, and then have a process to populate the data retrospectively. Therefore, we needed a solution that was more flexible.

The idea is to build a view on the columns to shred the data into a relational table, and then the reporting procedures would reference this view. I have been looking at the nodes() function, but have no idea whether this would suit our requirement.

The xml structure is very simplistic:

<DynoFormData xmlns="http://sysrepublic.com/Secure/4.0/DSL/ICMS/DynoFormData">
<incidentreportemployeeinjury>
<confirmationmessage value="Confirmation Message" />
<AllOtherElements value="another value" />
........
</incidentreportemployeeinjury>
</DynoFormData>


So we shall any number of elements contained in the 2 opening elements. It doesn't get any more complex than that. The only thing is that the nested elements will not be consistent across all the records in the xml column.

Confused dot com

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2013-03-18 : 12:31:31
so you want a consistent solution of extracting data from an xml that is never consistent?

:)

Why is the data inconsistent?
Can you change the incident tracking system to dump xml values even for parts of the form not filled in?

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion
Go to Top of Page

Bex
Aged Yak Warrior

580 Posts

Posted - 2013-03-18 : 13:18:27
Yep, that is exactly it! A consistent and fantastically performing solution...... for data that is inconsistent and in non-relational format :)

I don't think it is possible either, and I think that your suggestion may be the way to go. I'll have a word with the application developers and see what flexibility they are willing to offer me.

Thanks for answering
Go to Top of Page

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2013-03-18 : 13:50:21
:) make sure you sit down and map everything end to end with app developers. real scenarios and all possible permutations. if it does not work that they can change it it is possible to use a staging table to process your stuff with varchar and nullable.

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion
Go to Top of Page

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2013-03-18 : 15:32:26
One of many ways to shred and ingest


DECLARE @xmlDoc NVARCHAR(MAX), @handle INT


SET @xmlDoc =
'<DynoFormData xmlns:xsd="http://sysrepublic.com/Secure/4.0/DSL/ICMS/DynoFormData">
<incidentreportemployeeinjury>
<IncidentNo value = "12345"/>
<IncidentDate value = "01/12/2013"/>
<AssignedToName value = "Bex Starr"/>
<confirmationmessage value = "Confirmation Message"/>
<AllOtherElements value="another value" />
</incidentreportemployeeinjury>
</DynoFormData>'


EXEC sp_xml_preparedocument @handle OUTPUT, @xmlDoc

insert into [Incident]([IncidentNo], [IncidentDate], [AssignedToName])
SELECT IncidentNo, IncidentDate, AssignedToName
FROM OPENXML (@handle, '/DynoFormData/incidentreportemployeeinjury',1)
WITH (IncidentNo nvarchar(255) 'IncidentNo/@value',
IncidentDate datetime 'IncidentDate/@value',
AssignedToName nvarchar(255) 'AssignedToName/@value'
) a

select * From [Incident]




EXEC sp_xml_preparedocument @handle OUTPUT, @xmlDoc


<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion
Go to Top of Page
   

- Advertisement -