XML Workshop XI - Default Namespaces

  • jacob sebastian

    SSChampion

    Points: 11812

    Comments posted to this topic are about the item XML Workshop XI - Default Namespaces

    .

  • Hardy21

    SSCrazy Eights

    Points: 9708

    Hello,

    Thanks for your post. It is really useful.

    Currently I am facing an issue related to performace if XML is huge (nearly about 1 MB in size - 200 records). I am using SQL Server 2008.

    Our application is executing the stored procedure by passing XML as a input parameter. XML contains namespaces so I am using "WITH XMLNAMESPACES" command and use "FROM nodes" method - "T.C.value" to retrieve the information from XML file. I am parsing it and return the result set.

    Can you please guide me how can I improve the performance as well as steps needs to be taken to improve the performace?

    Thanks, Hardik

    Thanks

  • jacob sebastian

    SSChampion

    Points: 11812

    Two quick suggestions that I have are the following: (1) Try using TYPED XML (2) Try using XML Indexes.

    .

  • Hardy21

    SSCrazy Eights

    Points: 9708

    Thanks Jacob for your quick reply.

    I have checked that query is running perfectly fine and it is retrieving the data within 3 seconds but when I am using the function (XML data as input parameter) to retrieve the data, it is taking around 1 minute (I used same query in the function to parse the XML).

    For Example below query returns the data within 3 seconds:

    declare @XMLdata XML

    SET @XMLdata =

    '<XmlStart>..</XmlEnd>'

    ; WITH XMLNAMESPACES ( DEFAULT

    'http://abc.com',

    'http://hd.com' AS a

    'http://www.w3.org/2001/XMLSchema-instance' AS i)

    select

    T.C.value('a:data1[1]', 'smallint') AS Column1

    , T.C.value('(a:data2/a:data3)[1]', 'nvarchar(12)') AS Column2

    from @XMLdata.nodes('/XmlStart/Data/a:col1/a:col1Data') T ( C )

    But, when I use the same query in function & try to retrieve the data as below:

    declare @XMLdata XML

    SET @XMLdata =

    '<XmlStart>..</XmlEnd>'

    select * from hdFunction(@XMLdata)

    It is taking 1 minute or more than it.

    I need to use the function because lots of stored procedures are using the same XML. So, parsing code needs to be at one place. If is there any change in the business rule, it is easy to change at once place only.

    Please guide me to slove the issue.

    Thanks

  • jacob sebastian

    SSChampion

    Points: 11812

    Is this an INLINE Function? Can you post the create script (generate it using SSMS) of this function?

    .

  • Hardy21

    SSCrazy Eights

    Points: 9708

    Yes it is inline function. Function returns the table.

    CREATE FUNCTION hdFunction ( @XMLdata XML )

    RETURNS @Datatable TABLE

    (

    Col1 SMALLINT

    , Col2 NVARCHAR(12)

    )

    AS BEGIN

    WITH XMLNAMESPACES ( DEFAULT 'http://abc.com', 'http://hd.com' AS a,

    'http://www.w3.org/2001/XMLSchema-instance' AS i )

    INSERT INTO @Datatable

    SELECT T.C.value('a:data1[1]', 'smallint') AS Column1

    , T.C.value('(a:data2/a:data3)[1]', 'nvarchar(12)') AS Column2

    FROM @XMLdata.nodes('/XmlStart/Data/a:col1/a:col1Data') T ( C )

    RETURN

    END

    Thanks

  • jacob sebastian

    SSChampion

    Points: 11812

    Can you also post a sample XML document? I see you posted something, but the code-formatting must have eaten that. Try putting it between and blocks.

    .

  • Hardy21

    SSCrazy Eights

    Points: 9708

    Jacob, please find sample XML & its parsing function:

    declare @XMLdata XML

    SET @XMLdata =

    '<XmlStart xmlns="http://abc.com" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns:a="http://hd.com">

    <data>

    <a:Col1>

    <a:col1Data>

    <a:data1>5</a:data1>

    <a:data2>

    <a:data3>Hello</a:data3>

    </a:data2>

    </a:col1Data>

    </a:Col1>

    </data>

    </XmlStart>'

    CREATE FUNCTION hdFunction ( @XMLdata XML )

    RETURNS @Datatable TABLE

    (

    Col1 SMALLINT

    , Col2 NVARCHAR(12)

    )

    AS BEGIN

    WITH XMLNAMESPACES ( DEFAULT 'http://abc.com', 'http://hd.com' AS a,

    'http://www.w3.org/2001/XMLSchema-instance' AS i )

    INSERT INTO @Datatable

    SELECT T.C.value('a:data1[1]', 'smallint') AS Column1

    , T.C.value('(a:data2/a:data3)[1]', 'nvarchar(12)') AS Column2

    FROM @XMLdata.nodes('/XmlStart/Data/a:col1/a:col1Data') T ( C )

    RETURN

    END

    Thanks

  • jacob sebastian

    SSChampion

    Points: 11812

    Hmm..Just ran it and it worked well.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    (1 row(s) affected)

    Table '#3E52440B'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 11 ms.

    .

  • Hardy21

    SSCrazy Eights

    Points: 9708

    That was a sample XML and function which I am using to parse the XML and return the data.

    When XML has small numbers of data (suppose 2 to 3 records), it is returning data fast. But, when XML has around 175 to 200 records then it is taking lots of time to return - around 1.30 minutes.

    Please guide me to solve the issue.

    Thanks

  • jacob sebastian

    SSChampion

    Points: 11812

    I may be able to help only if I can take a look at the 'real' XML and 'real' code. If the XML does not have any sensitive data, you can upload it here or send me by email (jacob at beyondrelational dot com).

    .

  • BKAN

    SSC Rookie

    Points: 48

    Thanks Jacob for a very useful post. This helped me solve some issues.

    Thanks alot!

  • jacob sebastian

    SSChampion

    Points: 11812

    Glad to know it helped.

    .

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply