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

XML into SQL TABLES Expand / Collapse
Author
Message
Posted Friday, February 14, 2014 9:24 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 7:04 PM
Points: 69, Visits: 253
I have a database that contains one column and inside column thes data listed below, I want to insert id, type, target and targetmode into a sql table, any one can please help.
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId13" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/image" Target="../media/image357.png" />
<Relationship Id="rId18" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink" Target="PDF's/BB-7110.PDF" TargetMode="External" />
<Relationship Id="rId26" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink" Target="PDF's/VD16-A-0019.PDF" TargetMode="External" />
<Relationship Id="rId39" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink" Target="#'VS1RA GROUP 4'!A1" />
<Relationship Id="rId21" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/image" Target="../media/image361.png" />
</Relationships>
Post #1541649
Posted Friday, February 14, 2014 9:39 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 10:45 AM
Points: 99, Visits: 501
Try:

SET NOCOUNT ON;
USE tempdb;
GO
DECLARE @T TABLE (
sk int NOT NULL IDENTITY(1, 1) PRIMARY KEY,
x xml NOT NULL
);

INSERT INTO @T (x)
VALUES
('<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId13" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/image" Target="../media/image357.png" />
<Relationship Id="rId18" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink" Target="PDF''s/BB-7110.PDF" TargetMode="External" />
<Relationship Id="rId26" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink" Target="PDF''s/VD16-A-0019.PDF" TargetMode="External" />
<Relationship Id="rId39" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink" Target="#''VS1RA GROUP 4''!A1" />
<Relationship Id="rId21" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/image" Target="../media/image361.png" />
</Relationships>');

--id, type, target and targetmode
WITH XMLNAMESPACES(DEFAULT 'http://schemas.openxmlformats.org/package/2006/relationships')
SELECT
n1.x.value('@Id[1]', 'varchar(10)') AS Id,
n1.x.value('@Target[1]', 'varchar(256)') AS [Target],
n1.x.value('@TargetMode[1]', 'varchar(25)') AS [TargetMode]
FROM
@T AS T
CROSS APPLY
T.x.nodes('Relationships/Relationship') AS n1(x)
GO




Post #1541656
Posted Friday, February 14, 2014 4:45 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 7:04 PM
Points: 69, Visits: 253
WOW...
Thanks a lot..
Post #1541804
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse