Need help learning how to export data from SQL in XML form and then importing th

Question

Need help learning how to export data from SQL in XML form and then importing that data back into SQL. I am using the AdventureWorks2016 data downloaded from

https://www.microsoft.com/en-us/download/details.aspx?id=49502

This is my code so far for exporting:

Then I clicked the link to view the data as XML. Then I right clicked the query tab and selected "save as" and saved it as "xmlTest.xml".

So now.. trying to import it back into sql and this is my code so far:

I got an error saying that I had to change xml_test to xmlTest, and after I did it return a message stating that my "Commands completed successfully" but i thought it would bring up the data I am trying to import in a table form. Any ideas and what I am doing wrong?

Explanation / Answer

if you want data inXML document

then put query in BCP i.e Bulk copy

BCP " 'your query will come here ' FOR XML AUTO,TYPE, ELEMENTS ,ROOT('RegionSales')" QUERYOUT "/path/outputfile..XML" -c -t -T -S servername

following will SQL will open and read data and put it into column

-- declare variable to read file data
Declare @fileData XML

-- import the file contents into the variable
Select @fileDataX=BulkColumn from OpenRowSet(Bulk'pathilename.xml',Single_blob) x;

-- insert data into our table (columns names)
insert into TestingTable
(col1, col2,col3)
select
xData.value('col1[1]','data type') col1_name, -- 'xData' is our xml content alias
xData.value('col2[1]','data type with size') col2_name,
xData.value('col3[1]','data type with size') col3_name,
from @fileData.nodes('/RootNode/Child node') -- this is the xpath to the individual records we want to extract

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.