April 25, 2025 at 5:29 pm
Hello SSC,
Happy Friday!
I have to optimize a process that uses dynamic SQL to parse XML data. This is a daily process that is taking a long time to complete, and the data is not available until the afternoon. I installed SQLXML 4.0 and was able to SELECT nodes from the XML column directly, but I had to hardcode the node values and path for it to work. (Please see script below)
Instead of hard coding the values I am trying to provide the path using a variable as the XMLQuery.value part in the query below. So, @Nodelocation will be a parameter that is passed into the stored procedure. This cannot be hard coded since the parameter is dynamic.
Any assistance would be greatly appreciated! Please see code snippet below.
ERROR MESSAGE : The argument 1 of the XML data type method "value" must be a string literal.
drop table if exists #XMLParse
go
declare @Nodelocation nvarchar(max)
set @Nodelocation = '(node1/node2)[1]'
--print @Nodelocation
create table #XMLParse (XMLQuery XML);
insert into #XMLParse
select a.XmlData
FROM TBL1 a,
TBL2 b
WHERE b.ID = '12345';
--SELECT XMLQuery.value('(node/value)[1]', 'nvarchar(max)') AS Policy --This works!
SELECT XMLQuery.value(@Nodelocation, 'nvarchar(max)') AS Policy --This do NOT work
FROM #XMLParse;
The are no problems, only solutions. --John Lennon
April 25, 2025 at 5:55 pm
yes, it's annoying. you must literally use a string, and not a variable that happens to be a string.
The work around is to build the query string and execute it. ie
DECLARE @q varchar(max) = '
declare @xml xml
set @xml = ''<U>
<N id = "2" type ="good"/>
<N id = "2" type ="bad"/>
</U>''
select
row.value(''@id'', ''varchar(100)'') as id,
row.value(''@type'', ''varchar(100)'') as type,
row.query(''.'')
from @xml.nodes(''/U/N'') as xmltable(row)
where
row.value(''@type'', ''varchar(100)'') = ''good'' '
EXECUTE (@q)
if you are seeing a performance hit on your queries,. because that table with the xml is large, you can add xml indexes that will help substantially.
the index will depend on knowing an important node in the xml schema, i wish there was an easier way to discover the right way to build that xml index., but once discovered and built, like any index, i've seen 10x/100x improvements in performance
Lowell
April 28, 2025 at 3:25 am
You can pull this off with a few extra steps to split the input XPath into separate variables for each level in the XPath, and then performing the node test in the XQuery using //*[local-name()=sql:variable("@varablename")] for each level.
The sample below splits the XPath value 'xml/Type1b/Child1C' into three variables, and then pulls out the data using:
SELECT XmlData.value('(//*[local-name()=sql:variable("@path1")]//*[local-name()=sql:variable("@path2")]//*[local-name()=sql:variable("@path3")])[1]', 'varchar(32)') AS [Policy]
-- Build a sample table with two rows of XML data
-- The schema of the XML in each is unique, so parsing
-- data from out of the XML requires dynamic XPath
DROP TABLE IF EXISTS #XMLParse;
GO
SELECT 1 AS [id], CONVERT(xml, '
<xml>
<Type1a>
<Child1A>Policy 100a</Child1A>
<Child1B>Policy 110a</Child1B>
<Child1C>Policy 120a</Child1C>
</Type1a>
<Type2a>
<Child2A>Policy 200a</Child2A>
<Child2B>Policy 210a</Child2B>
<Child2C>Policy 220a</Child2C>
</Type2a>
</xml>') AS [XmlData]
INTO #XMLParse UNION ALL
SELECT 2, CONVERT(xml, '
<xml>
<Type1b>
<Child1A>Policy 100b</Child1A>
<Child1B>Policy 110b</Child1B>
<Child1C>Policy 120b</Child1C>
</Type1b>
<Type2b>
<Child2A>Policy 200b</Child2A>
<Child2B>Policy 210b</Child2B>
<Child2C>Policy 220b</Child2C>
</Type2b>
</xml>');
-- View source table with two rows of XML data.
SELECT * FROM #XMLParse;
-- These two values would be passed in as parameters
DECLARE @srch varchar(32) = 'xml/Type1b/Child1C',
@IDParameter int = 2;
-- Split the input node path into separate strings
DROP TABLE IF EXISTS #SrchPath;
CREATE TABLE #SrchPath(rn tinyint NOT NULL PRIMARY KEY, NodeVal varchar(128) NOT NULL);
-- Split the path parameter into elements
INSERT #SrchPath(rn, NodeVal)
SELECT [ordinal], [value]
FROM string_split(@srch, '/', 1)
WHERE len(value) > 0;
-- Need to use different queries based on the number of elements deep
-- the target value resides
-- This sample is for three elements:
DECLARE @path1 varchar(128) = (SELECT NodeVal FROM #SrchPath WHERE rn = 1),
@path2 varchar(128) = (SELECT NodeVal FROM #SrchPath WHERE rn = 2),
@path3 varchar(128) = (SELECT NodeVal FROM #SrchPath WHERE rn = 3);
-- dynamically specify each value for the node test for each element in the XPath
-- by comparing the node name (pulled using the XPath local-name() function) to
-- the desired node name for that level of the XPath expression (using the sql:variable()
-- XPath function)
SELECT XmlData.value('(//*[local-name()=sql:variable("@path1")]//*[local-name()=sql:variable("@path2")]//*[local-name()=sql:variable("@path3")])[1]', 'varchar(32)') AS [Policy]
FROM #XMLParse
WHERE id = @IDParameter;
-- The expected return from passing in 'xml/Type1b/Child1C' as the path to use
-- for ID number 2 is "Policy 120b"
Eddie Wuerch
MCM: SQL
April 28, 2025 at 6:44 am
The above example could be turned into a single statement:
-- Note:uses the #XMLParse table from the previous post
-- These two values would be passed in as parameters
DECLARE @srch varchar(32) = 'xml/Type1b/Child1C',
@IDParameter int = 2;
-- Turn this into a single statement:
WITH NodeList AS (
-- split the input into a table of values
SELECT [ordinal], [value]
FROM string_split(@srch, '/', 1)
WHERE len(value) > 0
),
NodeRow AS (
-- flatten the list into a single row with PIVOT
SELECT [1] AS [Node1], [2] AS [Node2], [3] AS [Node3]
FROM NodeList nl
PIVOT (MAX([value]) FOR [ordinal] IN ([1], [2], [3])) pvt
)
-- CROSS JOIN the single row output from the NodeRow CTE to
-- feed the XQuery.value function using sql:column()
SELECT XmlData.value('(//*[local-name()=sql:column("Node1")]//*[local-name()=sql:column("Node2")]//*[local-name()=sql:column("Node3")])[1]', 'varchar(32)') AS [Policy]
FROM #XMLParse
CROSS JOIN NodeRow nr
WHERE id = @IDParameter;
Eddie Wuerch
MCM: SQL
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply