Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A Generic Process to Convert XML Data - Part 1


A Generic Process to Convert XML Data - Part 1

Author
Message
Leo Peysakhovich
Leo Peysakhovich
SSC-Enthusiastic
SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)

Group: General Forum Members
Points: 163 Visits: 339
Comments posted to this topic are about the item A Generic Process to Convert XML Data - Part 1



tymberwyld
tymberwyld
SSC Veteran
SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)

Group: General Forum Members
Points: 286 Visits: 274
So, basically you are talking about an Audit table that just stores the "Audits" in a different Xml format? I've been doing that for a while but never relied on the Schemas (I guess I should). Basically, a View can be created for each "type" or "xml schema" so that if you DID need to see what audits took place, it's pretty easy to report on.

I've recently done something similar with regards to importing multiple Products into our database but support an old Xml format as well. So, basically I created two Xml Schema Collections (called them ProductSchema_v01 and ProductSchema_v02). The Stored Procedure then ensures that the Xml data coming in conforms to one or the other schemas and then imports the data appropriately based on the version.

CREATE PROCEDURE [dbo].[usp_ImportProducts]
(
@Products Xml
)
AS
Declare @v1 Xml (ProductSchema_v01)
Declare @v2 Xml (ProductSchema_v02)

BEGIN TRY
Set @v1 = @Products
END TRY
BEGIN CATCH
BEGIN TRY
Set @v2 = @Products
END TRY
BEGIN CATCH
RAISERROR('Invalid Xml...', 16, 1)
END CATCH
END CATCH

If (@v1 Is Not Null)
....
Else If (@v2 Is Not Null)
....





Anipaul
Anipaul
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6287 Visits: 1407
Good one...



Mike C
Mike C
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1353 Visits: 1168
Good article! One question did come to mind as I read it: would it be possible to modify your method to use a single XML schema for validation against multiple namespaces:


DROP XML SCHEMA COLLECTION stat;
GO

CREATE XML SCHEMA COLLECTION stat
AS
'<?xml version="1.0" encoding="ISO-8859-1" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" targetNamespace="leo:stat:a" xmlns="leo:stat:a">
<xs:element name="statroot">
<xs:complexType>
<xs:sequence>
<xs:element name="statrecord" minOccurs="0">
<xs:complexType>
<xs:sequence>
<xs:element name="companyid" type="xs:integer" />
<xs:element name="prodno" type="xs:string" />
<xs:element name="userid" type="xs:integer" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema> ';
GO

ALTER XML SCHEMA COLLECTION stat
ADD '<?xml version="1.0" encoding="ISO-8859-1" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" targetNamespace="leo:stat:b" xmlns="leo:stat:b">
<xs:element name="statroot">
<xs:complexType>
<xs:sequence>
<xs:element name="statrecord" minOccurs="0">
<xs:complexType>
<xs:sequence>
<xs:element name="co" type="xs:integer" />
<xs:element name="prod" type="xs:string" />
<xs:element name="uid" type="xs:integer" />
<xs:element name="ext" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema> '
GO

declare @xmlvar1 xml (stat);

set @xmlvar1 = '<xs:statroot xmlns:xs="leo:stat:a">
<statrecord>
<companyid>6</companyid>
<prodno>prd12</prodno>
<userid>12345</userid>
</statrecord>
</xs:statroot>
<xs:statroot xmlns:xs="leo:stat:b">
<statrecord>
<co>6</co>
<prod>prd12</prod>
<uid>12345</uid>
<ext>Test</ext>
</statrecord>
</xs:statroot>'



This would require you to pass in the namespace qualifiers in your input XML, but might eliminate the need to pass the XML schema collection name.

Thanks!
Leo Peysakhovich
Leo Peysakhovich
SSC-Enthusiastic
SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)

Group: General Forum Members
Points: 163 Visits: 339
You can do it. But I did not add it because I need to be able to get different XML schemas with different namespaces generically. You will see it in part 2. Sorry, but it has to be a drawing of the process but for some reason it didn't shows up. I did email to the publisher. They should correct it.



einman33
einman33
Mr or Mrs. 500
Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)

Group: General Forum Members
Points: 575 Visits: 512
Why is this a sql centric solution? Would performance be further improved with some C# code on an application server doing the xml heaving lifting?
tymberwyld
tymberwyld
SSC Veteran
SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)

Group: General Forum Members
Points: 286 Visits: 274
I don't see how performance could be improved by using an application solution. SQL is already set-based as can insert, update, and delete anything from the Xml you send in in one pass. CLR or any C# solution on the other hand will need to issue multiple statements to the database (and since you're talking about an application service - you also talking about multiple network passes back and forth between SQL Server). No, this would slow down performance drastically. You'd be surprised how efficient the Xml query engine is in SQL 2005 and up.



Mike C
Mike C
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1353 Visits: 1168
Eric Inman (7/3/2009)
Why is this a sql centric solution? Would performance be further improved with some C# code on an application server doing the xml heaving lifting?


If you're interested in guaranteeing data stored in the database is valid you have to use the database engine to check it, just like any other database-side constraints. You can always check data in client apps or in the middle tier before it gets stored in the database, but there's no guarantee that these methods of enforcing constraints on data can't be circumvented (by a DBA with direct table access, for instance).
einman33
einman33
Mr or Mrs. 500
Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)

Group: General Forum Members
Points: 575 Visits: 512
tymberwyld (7/3/2009)
I don't see how performance could be improved by using an application solution. SQL is already set-based as can insert, update, and delete anything from the Xml you send in in one pass. CLR or any C# solution on the other hand will need to issue multiple statements to the database (and since you're talking about an application service - you also talking about multiple network passes back and forth between SQL Server). No, this would slow down performance drastically. You'd be surprised how efficient the Xml query engine is in SQL 2005 and up.


The Insert Update and Delete are of course set based, parsing the DOM is about as RBAR as you can get. Who cares if its not apparnt to the developer writing the code. In modern web applications, round trips are not as costly as they used to be in client server set ups. The data tier and the web tier are usuallu a few firewalls away on a very fast backplane. That is no argument to introduct xpath and its friends into the data tier.

Do not even get me started with what the TDS protocol will do to a 200k xml blob. Which is what you will start getting if your app group reliazes you are in bed with the XML mistress.

Have you taken into account what XML parsing will do to tempdb? All of this for what, to be able to play with xpath in your stored procs. No thanks. XML is a data strucutre for the application tier.
einman33
einman33
Mr or Mrs. 500
Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)

Group: General Forum Members
Points: 575 Visits: 512
Mike C (7/4/2009)
Eric Inman (7/3/2009)
Why is this a sql centric solution? Would performance be further improved with some C# code on an application server doing the xml heaving lifting?


If you're interested in guaranteeing data stored in the database is valid you have to use the database engine to check it, just like any other database-side constraints. You can always check data in client apps or in the middle tier before it gets stored in the database, but there's no guarantee that these methods of enforcing constraints on data can't be circumvented (by a DBA with direct table access, for instance).


Understand, but I just dont think that passing around a string with over 95% waste in every packet gets that done. I enforce the data requirements in my domain, not the data interface. Over the last few years I have noticed that people are moving more to enforcing the domain data in the data interface layer and not in the domain iteslef. I dont know how this can hold up when said data becomes more than a trivial xml hierarchy.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search