Post reply

XML Workshop XI - Default Namespaces

  • November 21, 2007 at 12:06 am

    #127003

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

    .

  • April 12, 2010 at 2:51 am

    #1149871

    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

  • April 12, 2010 at 4:42 am

    #1149905

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

    .

  • April 12, 2010 at 5:29 am

    #1149930

    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

  • April 12, 2010 at 6:13 am

    #1149959

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

    .

  • April 12, 2010 at 6:37 am

    #1149976

    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

  • April 12, 2010 at 6:43 am

    #1149982

    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.

    .

  • April 12, 2010 at 6:56 am

    #1150002

    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

  • April 12, 2010 at 11:02 am

    #1150194

    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.

    .

  • April 12, 2010 at 10:36 pm

    #1150481

    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

  • April 13, 2010 at 1:07 am

    #1150512

    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).

    .

  • July 30, 2010 at 2:19 pm

    #1200540

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

    Thanks alot!

  • July 30, 2010 at 11:22 pm

    #1200605

    Glad to know it helped.

    .

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

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