Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Using T-SQL to Transform XML Data to a Relational Format Expand / Collapse
Author
Message
Posted Tuesday, December 29, 2009 8:20 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 10:54 AM
Points: 0, Visits: 27
Comments posted to this topic are about the item Using T-SQL to Transform XML Data to a Relational Format
Post #840158
Posted Wednesday, December 30, 2009 10:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 22, 2013 2:54 PM
Points: 27, Visits: 58
How about using OPENXML?

DECLARE @idoc int, @myXml xml

SET @myXml = '<Author xmlns:xsi="..." xmlns:xsd="..." version="1">
<Benefits>
<Benefit Name="HospitalizationLimit" Value="500000"/>
<Benefit Name="MedicalConsultationLimit" Value="12000"/>
<Benefit Name="LifeInsuranceCoverage" Value="1000000"/>
<Benefit Name="MonthlyTelephone" Value ="5000"/>
<Benefit Name="DependentMemberCount" Value="5"/>
...
</Benefits>
</Author>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @myXml

SELECT *
FROM OPENXML ( @idoc, '/Author/Benefits', 2 )
WITH (
HospitilizationLimit int './Benefit[@Name="HospitalizationLimit"]/@Value',
MedicalConsultationLimit int './Benefit[@Name="MedicalConsultationLimit"]/@Value'
)

EXEC sp_xml_removedocument @idoc


I find OPENXML to be fairly useful when translating XML to table data.

Hope this helps!

-Graham
Post #840450
Posted Wednesday, December 30, 2009 5:32 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, November 14, 2014 1:32 AM
Points: 43, Visits: 475
I'm glad you brought up OPENXML Graham. In the past I've used both methods (XQUERY, OPENXML) to process XML data.

MSDN suggests using OPENXML statements with XML data columns. But I can't see how this is possible and how it could be efficient.

If I have an XML column with XML indexes created on it would I want to pass column values to a sp_xml_preparedocument stored procedure and then use OPENXML statements?
Post #840633
Posted Wednesday, December 30, 2009 5:54 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 22, 2013 2:54 PM
Points: 27, Visits: 58
Gary,

I would think XML indexes are only useful across multiple rows, like when you want to find rows that contain certain data in the XML or when the XML column would appear in the WHERE clause of a query. If you are processing row-by-row, OPENXML would still be a good option.

Anyone have something else to add?

-Graham
Post #840639
Posted Thursday, December 31, 2009 6:56 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 10, 2014 7:22 AM
Points: 1,187, Visits: 1,984
Graham O'Daniel (12/30/2009)
How about using OPENXML?

I find OPENXML to be fairly useful when translating XML to table data.

Hope this helps!

-Graham

NO! See: Stop Using OPENXML
http://www.sqlservercentral.com/blogs/michael_coles/archive/2008/01/20/stop-using-openxml-please.aspx



(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.
Post #840813
Posted Thursday, December 31, 2009 9:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, November 14, 2014 1:32 AM
Points: 43, Visits: 475
Thanks for your post Mauve, it completely clears up what I suspected.

I've created a data feed that archives thoasands of XML files from a web-service, stores them in an indexed xml column and then shreds the data into complex relational hierarchies. The whole thing runs very quickly as a service on the production server. I think using the OPENXML technique will drain the resources and make other services on the server suffer.
Post #840882
Posted Friday, February 12, 2010 9:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 3, 2014 8:39 AM
Points: 3, Visits: 28
Any idea on how to do the same for a nested xml structure (e.g. a bill-of-material).
Post #864699
Posted Friday, February 12, 2010 9:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 22, 2013 2:54 PM
Points: 27, Visits: 58
Using OPENXML vs. .nodes is a trade off situation, each with good use cases.

You have to start by asking yourself what you want: performance or resource constraints. If you want performance and don't care about resources then OPENXML is your choice. If you are concerned about resource usage on the server then .nodes is your choice.

I wouldn't suggest one way over the other for all situations.

Please see:
http://sql-server-performance.com/Community/forums/p/25212/147146.aspx
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=93759
http://www.windows-tech.info/15/0a9fd8b0e4b36ccc.php
Post #864712
Posted Friday, February 12, 2010 9:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 22, 2013 2:54 PM
Points: 27, Visits: 58
@Mike: provide me an example of your XML and what you want, and I can write it up for you in both OPENXML and .nodes methods.

-Graham
Post #864717
Posted Wednesday, March 3, 2010 7:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 3, 2014 8:39 AM
Points: 3, Visits: 28
Below an example of the source xml I mean.

<Products>
<Product ID="123" Name="Male Bicycle" Amount="1">
<Product ID="988" Name="Male Frame" Amount="1"/>
<Product ID="501" Name="Wheel" Amount="2">
<Product ID="450" Name="Spoke" Amount="50"/>
<Product ID="490" Name="Rim" Amount="1"/>
</Product>
...
</Product>
<Product ID="234" Name="Female Bicycle" Amount="1">
<Product ID="998" Name="Female Frame" Amount="1"/>
<Product ID="501" Name="Wheel" Amount="2">
<Product ID="450" Name="Spoke" Amount="50"/>
<Product ID="490" Name="Rim" Amount="1"/>
</Product>
...
</Product>
</Products>

I would like to translate to two tables:

Table 1 = Products:
- Product ID
- Product Name
This table will only contain the distinct records from the xml. So product "Rim" will only appear once even though it has been defined for 20 bicycles.

Table 2 = BOM:
- Parent Product ID
- Component Product ID
- Amount
Post #875956
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse