These are the instructions:
Using the "AdventureWorksDW2016CTP3" database.
- Select * from Person.Person Where FirstName = John For XML Auto.
- Click on the XML link in the results area to open the XML view.
- Right click on the SSMS query tab above the view, and save the file as xmlTest.xml.
- Construct a SQL transaction to import the xmlTest.xml results file back into SQL Server. Hint: Use the Commands OPENROWSET and OPENXML and the two stored procedures sp_xml_preparedocument and sp_xml_removedocument.
I have already tried these queries & I am receiving error messages:
USE AdventureworksDW2016CTP3 GO DECLARE @XML AS XML, GhDoc AS INT, GSQL NVARCHAR (MX) SELECT @XML - BulkColumn FROM OPENROWSET (BULK 'C:\Users\amand Documents\SYM-408\xml Test.xml', SINGLE BLOB) AS x; SELECT * FROM Adventureworks2016CTP3. Person. Person WHERE FirstName = 'John' EXEC sp_xml preparedocument @hDoc OUTPUT, QXML SELECT FirstName FROM OPFNXMI (hoc., '/PERSONS/PERSON) WITH First Name [varchar](50) FirstName', Business EntityID varchar(100) Business EntityID" EXEC SP_xml_removedocument avoc 100 16 E Results Messages (50 LU c .el) "he XML Farve error 0x00ce555 uecled on line clumber 1, near the XL Lexl "LUXUusinese LILYTD>10334/ sugesunLilyIDXpernyrerINK/PersontypexNameSlyl0/NeLylexli. Mag 1:0, Oct, State, Procura ap_xml_pronarodneum , Line 1 [Hatch startina 2 The ATT rinttonia Only Yo l lamant TA Alw an XT camer... Ksy 0179, Lere-6, SLAL- 5, Line 10 Could no.. find prepared .. . with handile 0. Mng , leve , state, versp_n_remove document.inc 1 Match ATE 1 83 Xanl remorecocument: The value 8poiector Lasts ber 1 is invalid. Completion time: 2020-02-01 00:02:05.559.1519-07:00 Use AdventureworksDN201GCTP3 GO EVECLARE @x xml SELECT ex-D From OPENRONSET (DULK 'C:\Users\amand\Documents\SYM 488\xmlTest.xml', SINGLE_BLOB) AS Demographics (D) DECLARE @hdoc int EXEC sp xml preparedocument ehdoc OUTPUT, (x Select * From OPFNXML (@hdoc, '/Persons/Person', 2) WITH Business En LilyID inl, PersonType nvarchar(5), NameStyle nvarchar(5), FirstName nvarchar(15) MiddleName rivarchar(15) Las LName rivarchar(15), EmailPromotion nvarchar(20) rowguid nvarchar(30) ) EXEC sp xml removedocument @hdoc ulilyID10334C/Busines C....YDXCELSYPE>INS/De: ZULLT exame Lyl-OC/NameLY- 100% - Reeulte Messages The XML verse LOL 0xc000e505 CULLC Ou Line ..um: , Jee Lise KML L-X. "CIOX3u: Mag G002, Level 10, dlate 2, Procedure p_xel_propredocument, Line 1 1 rt line 21 The CYTOT 0.ription 13 'Only on concur alone in an XMT. Tent.'. Med 0179, Leve 16, Slale 3, Line 12 Could not find p l atiment with handico. Had ESIT), Tr. 16, Statoil, T. A Y A : Art, Tun 1 Ratch Star - Tinc spx.l cenu vedu gume..i Tuvaluy sup CVE LA Juml is invalid. Cration time: 261211-112-17T03:12:13.1176-163-T.: USE AdventureworksDW2016CTP3 GO DECLARE @XML AS XML, GhDoc AS INT, GSQL NVARCHAR (MX) SELECT @XML - BulkColumn FROM OPENROWSET (BULK 'C:\Users\amand Documents\SYM-408\xml Test.xml', SINGLE BLOB) AS x; SELECT * FROM Adventureworks2016CTP3. Person. Person WHERE FirstName = 'John' EXEC sp_xml preparedocument @hDoc OUTPUT, QXML SELECT FirstName FROM OPFNXMI (hoc., '/PERSONS/PERSON) WITH First Name [varchar](50) FirstName', Business EntityID varchar(100) Business EntityID" EXEC SP_xml_removedocument avoc 100 16 E Results Messages (50 LU c .el) "he XML Farve error 0x00ce555 uecled on line clumber 1, near the XL Lexl "LUXUusinese LILYTD>10334/ sugesunLilyIDXpernyrerINK/PersontypexNameSlyl0/NeLylexli. Mag 1:0, Oct, State, Procura ap_xml_pronarodneum , Line 1 [Hatch startina 2 The ATT rinttonia Only Yo l lamant TA Alw an XT camer... Ksy 0179, Lere-6, SLAL- 5, Line 10 Could no.. find prepared .. . with handile 0. Mng , leve , state, versp_n_remove document.inc 1 Match ATE 1 83 Xanl remorecocument: The value 8poiector Lasts ber 1 is invalid. Completion time: 2020-02-01 00:02:05.559.1519-07:00 Use AdventureworksDN201GCTP3 GO EVECLARE @x xml SELECT ex-D From OPENRONSET (DULK 'C:\Users\amand\Documents\SYM 488\xmlTest.xml', SINGLE_BLOB) AS Demographics (D) DECLARE @hdoc int EXEC sp xml preparedocument ehdoc OUTPUT, (x Select * From OPFNXML (@hdoc, '/Persons/Person', 2) WITH Business En LilyID inl, PersonType nvarchar(5), NameStyle nvarchar(5), FirstName nvarchar(15) MiddleName rivarchar(15) Las LName rivarchar(15), EmailPromotion nvarchar(20) rowguid nvarchar(30) ) EXEC sp xml removedocument @hdoc ulilyID10334C/Busines C....YDXCELSYPE>INS/De: ZULLT exame Lyl-OC/NameLY- 100% - Reeulte Messages The XML verse LOL 0xc000e505 CULLC Ou Line ..um: , Jee Lise KML L-X. "CIOX3u: Mag G002, Level 10, dlate 2, Procedure p_xel_propredocument, Line 1 1 rt line 21 The CYTOT 0.ription 13 'Only on concur alone in an XMT. Tent.'. Med 0179, Leve 16, Slale 3, Line 12 Could not find p l atiment with handico. Had ESIT), Tr. 16, Statoil, T. A Y A : Art, Tun 1 Ratch Star - Tinc spx.l cenu vedu gume..i Tuvaluy sup CVE LA Juml is invalid. Cration time: 261211-112-17T03:12:13.1176-163-T
