Hello!
There's an xml file config2.xml containing list of printing queues of a host (2 queues in the list)
The following script gives me 2 tables. One contains 2 SerialNumbers and the other 2 QueueNames
DECLARE @xml xml
SELECT @xml = C
FROM OPENROWSET (BULK 'd:\root\config2.xml', SINGLE_BLOB) AS Queues(C)
SELECT
doc.col.value('serial[1]', 'nvarchar(30)') Serial
FROM @xml.nodes('/config/queues/queue/assets') doc(col)
SELECT doc1.col.value('queue-name[1]','nvarchar(30)') QueueName
FROM
@xml.nodes('/config/queues/queue') doc1(col)
I'd like to create a single table containing 2 lines, each one with SerialNumber and the corresponding QueueName
The problem, as I see it, is that there's in the XML file there's no unique ID that could be used for Join operation
Would someone please suggest a solution?
April 15, 2022 at 9:31 pm
SELECT
HIGHERLEVEL.QueueName,
LOWERLEVEL.Serial
FROM
(
SELECT
row_number() over(order by (select 0)) as ID
,doc.col.value('serial[1]', 'nvarchar(30)') Serial
FROM
@xml.nodes('/config/queues/queue/assets') doc(col)
) as LOWERLEVEL
INNER JOIN
(
SELECT
row_number() over(order by (select 0)) as ID
,doc1.col.value('@id[1]','nvarchar(30)') as QueueName
FROM @xml.nodes('/config/queues/queue') doc1(col)
) as HIGHERLEVEL
ON
HIGHERLEVEL.ID = LOWERLEVEL.ID
The Serial value and the Queue name are both children of the queue nodes. Just use the path to the Serial value.
SELECT doc1.col.value('(@id)[1]', 'varchar(64)') AS [ID],
doc1.col.value('(queue-name)[1]', 'nvarchar(30)') AS [QueueName],
doc1.col.value('(assets/serial)[1]', 'nvarchar(30)') AS [Serial],
doc1.col.value('(@enabled)[1]', 'varchar(64)') AS [Enabled],
doc1.col.value('(@paused)[1]', 'varchar(64)') AS [Paused]
FROM @xml.nodes('/config/queues/queue') doc1(col);
Eddie Wuerch
MCM: SQL
April 21, 2022 at 1:48 pm
Thank you, Eddie! This is way more elegant than my approach
April 30, 2022 at 1:40 am
Hello!
Let me please ask you for advice again
Attached is another example of a xml file, now with 3 layers of data below point of entrance '/config/queues/queue', so to speak
For the moment, the following code
USE LSWDS
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
DECLARE @xml xml
DECLARE @DateTimeCurrent DATETIME
SET @DateTimeCurrent = Cast(SYSDATETIME() as smalldatetime)
SELECT @xml = C
FROM OPENROWSET (BULK 'D:\root\sls\configMultiLevels.xml', SINGLE_BLOB) AS Queues(C)
;with cteQueuesByConfig AS (
SELECT
--@DateTimeCurrentas cntDateTimeStamp
doc1.col.value('(assets/serial)[1]', 'nvarchar(30)') AS cntSerial
,doc1.col.value('queue-name[1]','nvarchar(50)') as cntQueueName
,doc1.col.value('queue-model[1]','nvarchar(50)') as cntMachineOS
,doc1.col.value('queue-group[1]','nvarchar(50)') as cntGroup
,doc1.col.value('install-date[1]','nvarchar(50)') as cntInstallDate
,doc1.col.value('(profile/@ping)[1]','nvarchar(50)') as cntProfilePing
,doc1.col.value('(profile/system/fqdns)[1]','nvarchar(50)') as cntFQDN
,doc1.col.value('(profile/spool/printers/printer/@name)[1]','nvarchar(50)') as cntPrinterName
,doc1.col.value('@id[1]','nvarchar(30)') as cntQueueID
FROM
@xml.nodes('/config/queues/queue') doc1(col)
)
SELECT * FROM cteQueuesByConfig
WHERE cntQueueID like '%remote%'
ORDER BY cntFQDN Asc
gives me just P1 line. I'd rather have 3 lines in the resulting table all of them with same cntProfilePing, cntQueueID but different cntPrinterName
How would you catch them all?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply