Issue with data population from XML

Question

I am reading data from XML into a table. When I do select from the table, the table is empty.

SET @INPUTXML = CAST(@Attribute AS XML)
EXEC Sp_xml_preparedocument @TestDoc OUTPUT, @INPUTXML

SELECT Row_Number() OVER (ORDER BY Name) AS Row, *
INTO #tData
FROM OPENXML(@TestDoc, N'/DocumentElement/dtData') 
WITH (
        ID VARCHAR(100) './ID'
        , Name VARCHAR(100) './Name'
        , Value VARCHAR(max) './Value'
        , Column VARCHAR(100) './Column'
)

EXEC Sp_xml_removedocument @TestDoc

Below are my questions:

select * from #tData is empty table. Why is data not getting populated?
What does Sp_xml_preparedocument do? When I print @TestDoc, it gives me a number
What is Sp_xml_removedocument ?

Why are you using `sp_xml_preparedocument`? XQUERY has been available since SQL Server 2005 (if I recall correctly, maybe 2008). Are you really still using SQL Server 2000? — Thom A, May 28 '19 at 15:28
But what version of SQL Server, @Learner? Either way, SSMS 2014 supported SQL Server 2008 to 2014, so you have access to XQUERY. Can you post some sample data and expect results here? — Thom A, May 28 '19 at 15:35
OPENXML is old school now. See https://stackoverflow.com/questions/61233/the-best-way-to-shred-xml-data-into-sql-server-database-columns — granadaCoder, May 28 '19 at 16:19

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

To answer your questions though.

#tData is empty because your SELECT statement returned no data. A SELECT...INTO statement will still create the table, even if the SELECT returns no rows. Why your SELECT is returning no data is impossible for us to say, because we have no sample data. If you remove the INTO clause you will see that no rows are returned, so you need to fix your SELECT, FROM, etc. but that brings on to my statement in a minute (about using XQUERY)
sp_xml_preparedocument (Transact-SQL) explains better than I could. Really though, you shouldn't be using it anymore, as it was used to read XML back in SQL Server 2000 (maybe 2005) and prior. Certainly SQL Server 2008 supported XQUERY, which you must be at least using if you are using SSMS 2014. To quote the opening statement of the documentation though:

Reads the XML text provided as input, parses the text by using the MSXML parser (Msxmlsql.dll), and provides the parsed document in a state ready for consumption. This parsed document is a tree representation of the various nodes in the XML document: elements, attributes, text, comments, and so on.
sp_xml_removedocument (Transact-SQL), but again, you should be using XQUERY.

Removes the internal representation of the XML document specified by the document handle and invalidates the document handle.

Issue with data population from XML

1 Answers1