Multi tool use

Dynamic index with XML path - SQL Server

I am trying to use dynamic index with xml path in below query, but it is not working. The concern is how to pass a variable with OPENXML(@hDoc, 'Department/dept[@i]')...

OPENXML(@hDoc, 'Department/dept[@i]')

declare @i int =1;

DECLARE @hDoc AS INT, @SQL NVARCHAR (MAX)
EXEC sp_xml_preparedocument @hDoc OUTPUT, @MBL

SELECT A, B, C, D
FROM OPENXML(@hDoc, 'Department/dept[@i]')
WITH (A [varchar](1000) 'Employees/Employee/@user',
B [varchar](1000) 'Employees/Employee/@name',
C [varchar](1000) 'Employees/Employee/@id',
D [varchar](1000) 'Employees/Employee/@date')

EXEC sp_xml_removedocument @hDoc
GO


1 Answer
1

I have a PROC I use at the moment which opens XML rows and outputs some of the info based on a field name parameter. I found the only way I could do this was through dynamic SQL. Not the slickest of solutions but may help you. This is a different technique for reading the XML so would require some reworking, but this may give you another option.

CREATE PROC [dba].[CheckChangeLog]
@TableName NVARCHAR(255) = 'dbo.OrderedItems'
, @RowID NVARCHAR(255) = ''
, @Fieldname NVARCHAR(255) = ''
AS
BEGIN
DECLARE @strSQL AS NVARCHAR(MAX)

SET @strSQL = '
SELECT ChangeLog.ID
, ChangeLog.TableName
, ChangeLog.Deleted
, ChangeLog.Inserted
, ChangeLog.DateTime
, d.value(''(./' + @Fieldname + ')[1]'', ''NVARCHAR(4000)'') AS FieldFrom
, i.value(''(./' + @Fieldname + ')[1]'', ''NVARCHAR(4000)'') AS FieldTo
FROM dba.ChangeLog WITH (NOLOCK)
OUTER APPLY Inserted.nodes(''/row'') Ins (i)
OUTER APPLY Deleted.nodes(''/row'') Del (d)
WHERE TableName = @TableName
AND (inserted IS NOT NULL
OR deleted IS NOT NULL
)
AND (i.value(''@id'', ''NVARCHAR(4000)'') = @RowID
OR i.value(''@id'', ''NVARCHAR(4000)'') IS NULL
)
AND (d.value(''@id'', ''NVARCHAR(4000)'') = @RowID
OR d.value(''@id'', ''NVARCHAR(4000)'') IS NULL
)
ORDER BY ID
'

EXEC sp_executesql
@strSQL
, N'@TableName NVARCHAR(255), @RowID NVARCHAR(255)'
, @TableName
, @RowID

END


I can't post examples of the XML its reading from as its company specific, but hopefully this will take you in the right direction.

The stored procedure is used for tracing changes on a table. If you need some more help on this I can post the table format and how to create the trigger that logs table changes. Is quite handy anyway.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.