xml..again

  • Hello everyone

    XML is killing me. It is my weekest point. I have another file that I should get into sql server express into a 2 column table: CUSTOMER SERVER, like this:

    customer 1 server1

    customer 1 server2

    customer 1 server3

    .......

    I've attached a sample file. It is as txt because xml is not allowed for upload Can you please help me with a code for it?

    Thank you

  • Can you try this..

    DECLARE @Xml TABLE ( XmlData XML );

    INSERT INTO @Xml

    ( XmlData

    )

    SELECT *

    FROM OPENROWSET(BULK N'YourXMLFilePath&Name', SINGLE_BLOB) O;

    SELECT [Row].value('@Customer', 'NVARCHAR(255)') AS Customer ,

    [Row].value('@Server', 'NVARCHAR(255)') AS Server

    FROM @Xml

    CROSS APPLY XmlData.nodes('//worksheet/table') AS [Table] ( [Row] );

  • Try:

    SET NOCOUNT ON;

    USE tempdb;

    GO

    DECLARE @Xml TABLE (XmlData XML);

    INSERT INTO @Xml

    ( XmlData

    )

    SELECT *

    FROM OPENROWSET(BULK N'C:\Temp\test.txt', SINGLE_BLOB) O;

    WITH XMLNAMESPACES (

    'http://www.w3.org/TR/REC-html40' AS ss,

    DEFAULT 'urn:schemas-microsoft-com:office:spreadsheet'

    )

    , C1 AS (

    SELECT

    N.x.value('(text())[1]', 'nvarchar(256)') AS val,

    ROW_NUMBER() OVER(ORDER BY N.x) AS rn

    FROM

    @Xml AS T

    CROSS APPLY

    T.XmlData.nodes('Workbook/Worksheet/Table/Row/Cell/Data') AS N(x)

    )

    , C2 AS (

    SELECT

    (rn - 1) / 2 bucket,

    ROW_NUMBER() OVER(PARTITION BY (rn - 1) / 2 ORDER BY rn) AS rn,

    val

    FROM

    C1

    )

    SELECT

    MAX(CASE WHEN rn = 1 THEN val END) AS customer,

    MAX(CASE WHEN rn = 2 THEN val END) AS [server]

    FROM

    C2

    GROUP BY

    bucket;

    GO

    The idea is to pull all [Data] nodes, enumerate them, identified buckets of two consecutives, enumerate them at the bucket level and then pivot.

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

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