Post reply

XML into SQL TABLES

  • February 14, 2014 at 9:24 am

    #285587

    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>

  • February 14, 2014 at 9:39 am

    #1689127

    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

  • February 14, 2014 at 4:45 pm

    #1689260

    WOW...

    Thanks a lot..

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply