February 27, 2014 at 5:11 am
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
February 27, 2014 at 8:15 am
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] );
February 27, 2014 at 12:57 pm
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