For part 1 of an assignment, I executed:
USE AdventureWorksDW2016CTP3
GO
SELECT * FROM AdventureWorks2016CTP3.Person.Person
WHERE FirstName = 'John'
FOR XML Auto
I now have to:
"
- 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:
USE AdventureworksDW2016CTP3
GO
SELECT *
FROM OPENROWSET(BULK 'C:\Users\Meagan\Documents\School\SYM-408\Assignment 3\xmlTest.xml', SINGLE_BLOB) AS x;
DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
SELECT * FROM AdventureWorks2016CTP3.Person.Person WHERE FirstName = 'John'
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
SELECT FirstName
FROM OPENXML(@hDoc, 'AdventureWorks2016CTP3.Person.Person BusinessEntityID="18334" PersonType="IN" NameStyle="0" FirstName="John" MiddleName="C" LastName="Anderson" EmailPromotion="0" rowguid="B3E71FA8-890F-40A5-A36E-01D7CFCAF4F7" ModifiedDate="2014-06-02T00:00:00" AlterCount="0"')
WITH
(
FirstName [varchar](50) '@FirstName',
BusinessEntityID [varchar](100) '@BusinessEntityID'
)
EXEC sp_xml_removedocument @hDoc
GO
I get the following error:
(1 row affected)
(58 rows affected)
Msg 6603, Level 16, State 2, Line 8
XML parsing error: Expected token 'eof' found 'NAME'.
AdventureWorks2016CTP3.Person.Person -->BusinessEntityID<--="18334" PersonType="IN" NameStyle="0" FirstName="John" MiddleName="C" LastName="Anderson" EmailPromotion="0" rowguid="B3E71FA8-890F-40A5-A36E-01D7CFCAF4F7" ModifiedDate="2014-06-02T00:00:00" AlterCount="0"
