SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to shred XML		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Bex
Aged Yak Warrior

United Kingdom
578 Posts
Posted - 03/18/2013 :  09:05:58  Show Profile  Send Bex an AOL message  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1608 Posts
Posted - 03/18/2013 :  12:31:31  Show Profile  Click to see yosiasz's MSN Messenger address  Reply with Quote
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

United Kingdom
578 Posts
Posted - 03/18/2013 :  13:18:27  Show Profile  Send Bex an AOL message  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1608 Posts
Posted - 03/18/2013 :  13:50:21  Show Profile  Click to see yosiasz's MSN Messenger address  Reply with Quote
:) 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
Flowing Fount of Yak Knowledge

USA
1608 Posts
Posted - 03/18/2013 :  15:32:26  Show Profile  Click to see yosiasz's MSN Messenger address  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000