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 Wednesday, March 3, 2010 9:13 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, this should do the trick. OPENXML method first followed by the .nodes() method.

declare @xml xml
set @xml = '<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>'

declare @Products table ( ProductID int, ProductName varchar(50) )
declare @BOM table ( ParentProductID int, ComponentProductID int, Amount int )

declare @idoc int

exec sp_xml_preparedocument @idoc output, @xml

insert into @Products ( ProductID, ProductName )
select distinct
ID
, Name
from OPENXML ( @idoc, '//Product', 2 )
with (
ID int '@ID'
, Name varchar(50) '@Name'
)

insert into @BOM ( ParentProductID, ComponentProductID, Amount )
select
ParentProductID
, ComponentProductID
, Amount
from OPENXML ( @idoc, '//Product', 2 )
with (
ParentProductID int '../@ID'
, ComponentProductID int '@ID'
, Amount int '@Amount'
)

exec sp_xml_removedocument @idoc

select * from @Products
select * from @BOM

delete @Products
delete @BOM

insert into @Products ( ProductID, ProductName )
select distinct
node.value ( '(@ID)[1]', 'int' )
, node.value ( '(@Name)[1]', 'varchar(50)' )
from @xml.nodes ( '//Product' ) p(node)

insert into @BOM ( ParentProductID, ComponentProductID, Amount )
select
node.value ( '(../@ID)[1]', 'int' )
, node.value ( '(@ID)[1]', 'int' )
, node.value ( '(@Amount)[1]', 'int' )
from @xml.nodes ( '//Product' ) p(node)

select * from @Products
select * from @BOM

Post #876083
Posted Wednesday, November 2, 2011 10:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 14, 2012 4:46 PM
Points: 10, Visits: 87
Thanks for all of this it was exactly what I was trying to solve. I'm using the XQUERY and have all good results except one particular node format.

The node looks like:
<ProductID ProductNo="100086861"></ProductID>

I'd be happy to get the full string but really only need that number.

Using the XQUERY approach can that be done?

Thanks for your time.

P
Post #1199435
Posted Wednesday, November 2, 2011 10:51 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
paul.morgan-1069605 (11/2/2011)
Thanks for all of this it was exactly what I was trying to solve. I'm using the XQUERY and have all good results except one particular node format.

The node looks like:
<ProductID ProductNo="100086861"></ProductID>

I'd be happy to get the full string but really only need that number.

Using the XQUERY approach can that be done?

Thanks for your time.

P

(Using SQL Server 2008 R2)
DECLARE @xml xml = '<ProductID ProductNo="100086861"></ProductID>';
SELECT @xml.value('/ProductID[1]/@ProductNo', 'int');




(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 #1199451
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse