Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Sales Order Workshop Part IV Expand / Collapse
Author
Message
Posted Friday, April 13, 2007 4:26 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
I just posted a reply above, where i presented an example which extracts values from attributes and XML nodes.

.
Post #358137
Posted Friday, April 13, 2007 4:28 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
I just posted a reply above, which has an example of extracting values from xml attributes and nodes, using "value" method.

.
Post #358138
Posted Thursday, June 7, 2007 11:58 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 17, 2014 8:51 AM
Points: 42, Visits: 104

Thanks for the additional info. That makes a lot of sense. One more question: how do you incorporate namespaces into the processing? Do you have to just pull the namespaces out, or can you include the, for example, 'my:element' in its entirety. Apologies if this has already been addressed. I didn't see it above.

Thanks,

Ryan

Post #372118
Posted Friday, June 8, 2007 1:09 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523

Hi Ryan,

the following example shows how to take values from a namespace.

declare @x xml

set @x = '

<ns0:root xmlns:ns0="http://schemas.microsoft.com/sqlserver/2004/

07/adventure-works/ProductModelManuInstructionsModified">

<ns0:Location LocationID="100" SetupHours="10.4"

MachineHours="10.4" LaborHours="10.4" LotSize="10.4" />

</ns0:root>'

select

x.ns.value('@LocationID[1]', 'varchar(10)') as LocationID

from

@x.nodes('declare namespace ns0="http://schemas.microsoft.com/sqlserver/2004/

07/adventure-works/ProductModelManuInstructionsModified";/ns0:root/ns0:Location') as x(ns)



.
Post #372251
Posted Wednesday, August 22, 2007 5:18 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 5, 2013 7:04 AM
Points: 1,197, Visits: 294

tymberwyld:

Why not use NULLIF( LTrim(RTrim(Prefix)) , '' ) instead of Case When LTrim(RTrim(Prefix)) = '' Then Null Else LTrim(RTrim(Prefix)) End As Prefix?

Regards,

Hanslindgren




Post #392704
Posted Wednesday, August 22, 2007 4:53 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, February 2, 2013 8:21 AM
Points: 283, Visits: 268
Heh, you got me, that would be easier...


Post #393016
Posted Monday, September 29, 2008 5:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, January 8, 2013 11:12 PM
Points: 284, Visits: 129
I am using approach mentioned in post to do a master detail insert with following query,

INSERT INTO LISTDTL(ListDtl_ListHdrID,ListDtl_ProductID,ListDtl_ISBN_Number) SELECT @ListHdr_ID,P.PROD_PRODUCT_ID,ISBN.ISBNNumber FROM PRODUCT P INNER JOIN (SELECT ParamValues.ID.value('.','VARCHAR(20)') as ISBNNumber FROM @ValidISBN.nodes('/VALIDISBN/value') as ParamValues(ID) ) ISBN ON P.PROD_NUMBER=ISBN.ISBNNumber

where,
@ValidISBN='VALIDISBN Value 89869855898955 /value /VALIDISBN'

(This XML string. I could not write in proper XML as it was showing blank if it is written in proper XML format. This string is created in front end of application and is set to sql command parameter when using ExecuteNonQuery)

If @ValidISBN contains 7000 records, my insert query takes around 1 min or more.

How can I improve performance of my query.
Post #577624
Posted Monday, September 29, 2008 9:01 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 5, 2013 7:04 AM
Points: 1,197, Visits: 294
Hi,

Well, XML is not thought for performance. If you are processing 7000+ rows and you want real performance, you should use CSV files or similar files with much less overhead. You can even gain performance by having a fast XML -> CSV parser first in your chain.
Otherwise, try other T-SQL XML parsing approaches (like with OPENXML or similar) to find out which one is faster.



Post #577773
Posted Monday, September 29, 2008 9:09 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
Agreed with Hans. On large XML documents, OPENXML is found to be performing better than XQuery methods.

.
Post #577781
Posted Wednesday, October 1, 2008 3:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, January 8, 2013 11:12 PM
Points: 284, Visits: 129
Thanks all for your reply. I reverted to CSV string and used a function to return table from it. This table is used in my join query. I was amazed to see CSV query completing insert in just 14 sec in comparison to 2:05 sec using XML string. Still not able to get why XML query is taking such a long time.
Post #578914
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse