Parse XML data from SQL table using temp tables and variables

  • 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

  • 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

     

    • This reply was modified 3 weeks, 2 days ago by  Lowell.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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

  • 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