Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


XML Workshop XI - Default Namespaces


XML Workshop XI - Default Namespaces

Author
Message
jacob sebastian
jacob sebastian
SSChasing Mays
SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)

Group: General Forum Members
Points: 618 Visits: 2523
Comments posted to this topic are about the item XML Workshop XI - Default Namespaces

.
Hardy21
Hardy21
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1324 Visits: 1399
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
jacob sebastian
SSChasing Mays
SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)

Group: General Forum Members
Points: 618 Visits: 2523
Two quick suggestions that I have are the following: (1) Try using TYPED XML (2) Try using XML Indexes.

.
Hardy21
Hardy21
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1324 Visits: 1399
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
jacob sebastian
SSChasing Mays
SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)

Group: General Forum Members
Points: 618 Visits: 2523
Is this an INLINE Function? Can you post the create script (generate it using SSMS) of this function?

.
Hardy21
Hardy21
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1324 Visits: 1399
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
jacob sebastian
SSChasing Mays
SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)

Group: General Forum Members
Points: 618 Visits: 2523
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
Hardy21
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1324 Visits: 1399
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
jacob sebastian
SSChasing Mays
SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)

Group: General Forum Members
Points: 618 Visits: 2523
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
Hardy21
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1324 Visits: 1399
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search