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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy