SQL XML Import

%3CLINGO-SUB%20id%3D%22lingo-sub-170179%22%20slang%3D%22en-US%22%3ESQL%20XML%20Import%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-170179%22%20slang%3D%22en-US%22%3E%3CP%3EFor%20part%201%20of%20an%20assignment%2C%20I%20executed%3A%3C%2FP%3E%0A%3CP%3EUSE%20AdventureWorksDW2016CTP3%3CBR%20%2F%3EGO%20%3CBR%20%2F%3ESELECT%20*%20FROM%20AdventureWorks2016CTP3.Person.Person%3CBR%20%2F%3EWHERE%20FirstName%20%3D%20'John'%3CBR%20%2F%3EFOR%20XML%20Auto%3C%2FP%3E%0A%3CP%3EI%20now%20have%20to%3A%3C%2FP%3E%0A%3CP%3E%22%3C%2FP%3E%0A%3COL%3E%0A%3CLI%3EConstruct%20a%20SQL%20transaction%20to%20import%20the%20%E2%80%9CxmlTest.xml%E2%80%9D%20results%20file%20back%20into%20SQL%20Server.%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3EHint%3A%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3EUse%20the%20Commands%20OPENROWSET%20and%20OPENXML%20and%20the%20two%20stored%20procedures%20%E2%80%9Csp_xml_preparedocument%E2%80%9D%20and%20%E2%80%9Csp_xml_removedocument.%E2%80%9D%22%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3EI%20have%3A%3C%2FP%3E%0A%3CP%3EUSE%20AdventureworksDW2016CTP3%3C%2FP%3E%0A%3CP%3EGO%3C%2FP%3E%0A%3CP%3ESELECT%20*%3C%2FP%3E%0A%3CP%3EFROM%20OPENROWSET(BULK%20'C%3A%5CUsers%5CMeagan%5CDocuments%5CSchool%5CSYM-408%5CAssignment%203%5CxmlTest.xml'%2C%20SINGLE_BLOB)%20AS%20x%3B%3C%2FP%3E%0A%3CP%3EDECLARE%20%40XML%20AS%20XML%2C%20%40hDoc%20AS%20INT%2C%20%40SQL%20NVARCHAR%20(MAX)%3C%2FP%3E%0A%3CP%3ESELECT%20*%20FROM%20AdventureWorks2016CTP3.Person.Person%20WHERE%20FirstName%20%3D%20'John'%3C%2FP%3E%0A%3CP%3EEXEC%20sp_xml_preparedocument%20%40hDoc%20OUTPUT%2C%20%40XML%3C%2FP%3E%0A%3CP%3ESELECT%20FirstName%3C%2FP%3E%0A%3CP%3EFROM%20OPENXML(%40hDoc%2C%20'AdventureWorks2016CTP3.Person.Person%20BusinessEntityID%3D%2218334%22%20PersonType%3D%22IN%22%20NameStyle%3D%220%22%20FirstName%3D%22John%22%20MiddleName%3D%22C%22%20LastName%3D%22Anderson%22%20EmailPromotion%3D%220%22%20rowguid%3D%22B3E71FA8-890F-40A5-A36E-01D7CFCAF4F7%22%20ModifiedDate%3D%222014-06-02T00%3A00%3A00%22%20AlterCount%3D%220%22')%3C%2FP%3E%0A%3CP%3EWITH%3C%2FP%3E%0A%3CP%3E(%3C%2FP%3E%0A%3CP%3EFirstName%20%5Bvarchar%5D(50)%20'%40FirstName'%2C%3C%2FP%3E%0A%3CP%3EBusinessEntityID%20%5Bvarchar%5D(100)%20'%40BusinessEntityID'%3C%2FP%3E%0A%3CP%3E)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEXEC%20sp_xml_removedocument%20%40hDoc%3C%2FP%3E%0A%3CP%3EGO%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20get%20the%20following%20error%3A%3C%2FP%3E%0A%3CP%3E(1%20row%20affected)%3C%2FP%3E%0A%3CP%3E(58%20rows%20affected)%3CBR%20%2F%3EMsg%206603%2C%20Level%2016%2C%20State%202%2C%20Line%208%3CBR%20%2F%3EXML%20parsing%20error%3A%20Expected%20token%20'eof'%20found%20'NAME'.%3C%2FP%3E%0A%3CP%3EAdventureWorks2016CTP3.Person.Person%20--%26gt%3BBusinessEntityID%26lt%3B--%3D%2218334%22%20PersonType%3D%22IN%22%20NameStyle%3D%220%22%20FirstName%3D%22John%22%20MiddleName%3D%22C%22%20LastName%3D%22Anderson%22%20EmailPromotion%3D%220%22%20rowguid%3D%22B3E71FA8-890F-40A5-A36E-01D7CFCAF4F7%22%20ModifiedDate%3D%222014-06-02T00%3A00%3A00%22%20AlterCount%3D%220%22%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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:

"

  1. 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"

0 Replies