2

I am trying to flatten the XML into SQL table using below code. The input datatable (@incomingTable) contains 10k un typed XMLs the query takes 7 sec to return the output. When I checked the Execution Plan found most of the time is spent on "Table Vaued Functions (XML Reader with XPATH filter)" step. My guess is this step refers to Value method in query.

The value() method uses the Transact-SQL CONVERT operator implicitly and tries to convert the result of the XQuery expression, to the corresponding SQL type specified by Transact-SQL conversion.

Questions:Is there any other XML method to retrieve element/attribute value without data type converting . because I want the data as string anyhow. Helps me compare the results for two approaches.

Is there any other way to optimize this query?

select 
sqlXml.value('@ID', 'varchar(50)') as XMLFieldName,
sqlXml.value('@TS', 'varchar(50)') as XMLTSValue,
sqlXml.value('.','varchar(800)') as XMLFieldValue
from @incomingTable
cross apply playfieldvalues.nodes('/PlayAttributes/PlayFields/PlayField') as 
XMLData(sqlXml)
user1745679
  • 209
  • 1
  • 2
  • 9
  • Have you tried `OPENXML`? https://msdn.microsoft.com/en-us/library/ms186918(v=sql.110).aspx?f=255&MSPPError=-2147217396 – gofr1 Dec 07 '17 at 14:06
  • OPENXML returns output in columns I want the data in rows. Is that possible? – user1745679 Dec 07 '17 at 14:26
  • Well... I see your query - it returns 3 columns. Please, elaborate. OPENXML can do the same thing. If you have huge XML - it will work more faster than `nodes`. – gofr1 Dec 07 '17 at 14:30
  • I think I got your question. Please, refer to my answer. – gofr1 Dec 07 '17 at 14:44
  • The only tiny enhancement might be to use `text()[1]` instead of `.`, but this will not deliver much faster... How is this `@incomingTable` provided? The most expenisve process is - in most cases - the transfer of string-XML to the native XML type (which is a hierarchy table) – Shnugo Dec 07 '17 at 16:40

1 Answers1

-1

Try to use OPENXML:

DECLARE @idoc int;  

EXEC sp_xml_preparedocument @idoc OUTPUT, @incomingTable; 

SELECT    *  
FROM       OPENXML (@idoc, '/PlayAttributes/PlayFields/PlayField',1)  
            WITH (XMLFieldName  varchar(50) '@ID',  
                  XMLTSValue varchar(50) '@TS',
                  XMLFieldValue varchar(800) '.');  

EXEC sp_xml_removedocument @idoc;

OPENXML allows accessing XML data as if it were a relational recordset. It provides a tabular (rowset) view of in-memory representation of an XML document. Technically, OPENXML is a rowset provider similar to a table or a view; hence it can be used wherever a table or a view is used. For instance, you can use OPENXML with SELECT or SELECT INTO statements, to work on an XML document that is retained in memory.

Source

gofr1
  • 15,741
  • 11
  • 42
  • 52
  • I am trying this example. Will keep you posted. Thanks. – user1745679 Dec 07 '17 at 15:44
  • I am getting error: Msg 257, Level 16, State 31, Procedure sp_xml_preparedocument, Line 1 [Batch Start Line 0] Implicit conversion from data type void type to ntext is not allowed. Use the CONVERT function to run this query." – user1745679 Dec 07 '17 at 16:18
  • My XML structure is 10 17 7 A 2 +2 1 FG ' – user1745679 Dec 07 '17 at 16:18
  • expecting the O/P with three columns as Play.Shared.gameKey 56492 Football.Str_D 1/3/2015 12:00:00 AM Play.Quarter 1 Play.Shared.Time_of_Day 01:39:47 – user1745679 Dec 07 '17 at 16:20
  • it is working now. I am trying to make it work with bunch of xmls at same time than processing one xml at a time. – user1745679 Dec 07 '17 at 16:47
  • I'd assume, that `FROM OPENXML` might be even slower - as long as the column within your table is natively typed as `XML` already. The most expensive part is the parsing process. `FROM OPENXML` will - quite probably - have to 1) create the XML as its string-representation and 2) re-parse the whole lot... With a big bunch of records (10k is quite some space...) 7 seconds might be okay... – Shnugo Dec 07 '17 at 17:02
  • I still need to make the openxml work with bulk data so I can compare the two methods. – user1745679 Dec 07 '17 at 17:18
  • You can build one big xml from the table and use openxml on it – gofr1 Dec 07 '17 at 17:57
  • @gofr1 I can tell you for sure, that this will be slower... `FROM OPENXML` is - by design - not set-based. You'd need a `CURSOR` (or other loop) to work down the line. Combining all these XMLs to one big document will add huge extra load... – Shnugo Dec 08 '17 at 15:26