sql - 如何修复解析为 SQL 时 XML 文档错误中只允许一个顶级元素
问题描述
我正在尝试构建一个将 XML 文件导入 SQL 服务器的 SQL 事务。但是，这样做时我收到此错误：
XML 解析错误 0xc00ce555 发生在第 1 行，靠近 XML 文本“13.992004-07-03Z1972-09-25ZS75001-100000M00Bachelors Professional1410+ Miles
消息 6602，级别 16，状态 2，过程 sp_xml_preparedocument，第 1 行 [批处理开始第 0 行]
错误描述是“XML 文档中只允许一个顶级元素。”。消息 8179，级别 16，状态 5，第 13 行
找不到句柄为 0 的准备好的语句。消息 6607，级别 16，状态 3，过程 sp_xml_removedocument，第 1 行 [批处理开始行 0]
sp_xml_removedocument：为参数编号 1 提供的值无效。
我尝试将所有 XML 包装在一个<root></root>标签中，因为我的印象是我的 XML 文件有多个根，我知道这是不允许的。但是文件本身没有。
这是我的 SQL 查询
DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
SELECT @XML = BulkColumn
FROM OPENROWSET(BULK 'C:\Users\Slick\desktop\xmlTest.xml', SINGLE_BLOB) AS x;
SELECT *
FROM AdventureWorks2016CTP3.Person.Person
WHERE FirstName = 'John'
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
SELECT FirstName
FROM OPENXML(@hDoc, '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"')
WITH
(
FirstName [VARCHAR](50) '@FirstName',
BusinessEntityID [VARCHAR](100) '@BusinessEntityID'
)
EXEC sp_xml_removedocument @hDoc
这是我的 XML 文件
<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">
<Demographics>
<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
<TotalPurchaseYTD>13.99</TotalPurchaseYTD>
<DateFirstPurchase>2004-07-03Z</DateFirstPurchase>
<BirthDate>1972-09-25Z</BirthDate>
<MaritalStatus>S</MaritalStatus>
<YearlyIncome>75001-100000</YearlyIncome>
<Gender>M</Gender>
<TotalChildren>0</TotalChildren>
<NumberChildrenAtHome>0</NumberChildrenAtHome>
<Education>Bachelors </Education>
<Occupation>Professional</Occupation>
<HomeOwnerFlag>1</HomeOwnerFlag>
<NumberCarsOwned>4</NumberCarsOwned>
<CommuteDistance>10+ Miles</CommuteDistance>
</IndividualSurvey>
</Demographics>
</Person.Person>
<Person.Person BusinessEntityID="371" PersonType="SC" NameStyle="0" Title="Mr." FirstName="John" LastName="Arthur" EmailPromotion="0" rowguid="902112B6-7FE7-4E85-8D77-FF49C5E6761A" ModifiedDate="2011-07-01T00:00:00">
<Demographics>
<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
<TotalPurchaseYTD>-7693.8214</TotalPurchaseYTD>
</IndividualSurvey>
</Demographics>
</Person.Person>
<Person.Person BusinessEntityID="377" PersonType="SC" NameStyle="0" Title="Mr." FirstName="John" MiddleName="P." LastName="Ault" EmailPromotion="0" rowguid="F0B67A75-4235-45D5-A5E0-2E938A975DE8" ModifiedDate="2013-06-30T00:00:00">
<Demographics>
<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
<TotalPurchaseYTD>-8648.19</TotalPurchaseYTD>
</IndividualSurvey>
</Demographics>
</Person.Person>
<Person.Person BusinessEntityID="429" PersonType="SC" NameStyle="0" Title="Mr." FirstName="John" MiddleName="A." LastName="Beaver" EmailPromotion="0" rowguid="011DD8D5-1AC4-4FE8-B139-EE9D0EA172E6" ModifiedDate="2013-02-28T00:00:00">
<Demographics>
<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
<TotalPurchaseYTD>-131.2036</TotalPurchaseYTD>
</IndividualSurvey>
</Demographics>
</Person.Person>
<Person.Person BusinessEntityID="445" PersonType="SC" NameStyle="0" Title="Mr." FirstName="John" MiddleName="M." LastName="Bennetts" EmailPromotion="0" rowguid="694258A3-633C-42F9-98B8-08E50EDEDC68" ModifiedDate="2013-07-31T00:00:00">
<Demographics>
<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
<TotalPurchaseYTD>-729.438</TotalPurchaseYTD>
</IndividualSurvey>
</Demographics>
</Person.Person>
<Person.Person BusinessEntityID="465" PersonType="SC" NameStyle="0" Title="Mr." FirstName="John" MiddleName="M." LastName="Berger" Suffix="Jr." EmailPromotion="0" rowguid="B901DD8D-087B-49BE-BF1F-3B11F6D95820" ModifiedDate="2011-07-01T00:00:00">
<Demographics>
<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
<TotalPurchaseYTD>-11626.0099</TotalPurchaseYTD>
</IndividualSurvey>
</Demographics>
</Person.Person>
<Person.Person BusinessEntityID="475" PersonType="SC" NameStyle="0" FirstName="John" LastName="Berry" EmailPromotion="0" rowguid="8F36A32A-BC2C-4313-910C-E7A1E2D13045" ModifiedDate="2013-06-30T00:00:00">
<Demographics>
<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
<TotalPurchaseYTD>-16463.544</TotalPurchaseYTD>
</IndividualSurvey>
</Demographics>
</Person.Person>
<Person.Person BusinessEntityID="533" PersonType="SC" NameStyle="0" Title="Mr." FirstName="John" MiddleName="R." LastName="Brooks" EmailPromotion="0" rowguid="4B21D63C-181F-404A-A1AB-03EDF4E1D478" ModifiedDate="2012-05-30T00:00:00">
<Demographics>
<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
<TotalPurchaseYTD>-3947.6869</TotalPurchaseYTD>
</IndividualSurvey>
</Demographics>
</Person.Person>
<Person.Person BusinessEntityID="18292" PersonType="IN" NameStyle="0" FirstName="John" LastName="Brown" EmailPromotion="0" rowguid="9D88EA61-6B2C-430F-9FFF-68D4107EB044" ModifiedDate="2013-10-12T00:00:00">
<Demographics>
<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
<TotalPurchaseYTD>-9.99</TotalPurchaseYTD>
<DateFirstPurchase>2003-11-13Z</DateFirstPurchase>
<BirthDate>1956-06-10Z</BirthDate>
<MaritalStatus>M</MaritalStatus>
<YearlyIncome>50001-75000</YearlyIncome>
<Gender>M</Gender>
<TotalChildren>3</TotalChildren>
<NumberChildrenAtHome>2</NumberChildrenAtHome>
<Education>Partial College</Education>
<Occupation>Professional</Occupation>
<HomeOwnerFlag>1</HomeOwnerFlag>
<NumberCarsOwned>1</NumberCarsOwned>
<CommuteDistance>5-10 Miles</CommuteDistance>
</IndividualSurvey>
</Demographics>
</Person.Person>
<Person.Person BusinessEntityID="2105" PersonType="GC" NameStyle="0" FirstName="John" LastName="Campbell" EmailPromotion="0" rowguid="D9533653-5147-4F7D-A904-6E7801E50F91" ModifiedDate="2011-05-24T00:00:00">
<Demographics>
<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
<TotalPurchaseYTD>0</TotalPurchaseYTD>
</IndividualSurvey>
</Demographics>
</Person.Person>
<Person.Person BusinessEntityID="62" PersonType="EM" NameStyle="0" FirstName="John" MiddleName="T" LastName="Campbell" EmailPromotion="1" rowguid="F5A4CC1B-AEFA-46CA-8888-2CA1C6D4034B" ModifiedDate="2014-02-07T00:00:00">
<Demographics>
<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
<TotalPurchaseYTD>0</TotalPurchaseYTD>
</IndividualSurvey>
</Demographics>
</Person.Person>
<Person.Person BusinessEntityID="2329" PersonType="GC" NameStyle="0" FirstName="John" LastName="Chen" EmailPromotion="1" rowguid="185EDBE3-5FEA-4FD7-A0C6-F1E26EE13FC1" ModifiedDate="2009-01-30T00:00:00">
<Demographics>
<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
<TotalPurchaseYTD>0</TotalPurchaseYTD>
</IndividualSurvey>
</Demographics>
</Person.Person>
<Person.Person BusinessEntityID="184" PersonType="EM" NameStyle="0" FirstName="John" MiddleName="Y" LastName="Chen" EmailPromotion="2" rowguid="5E6EC398-C951-44A2-BEC7-DC1CD17FAACC" ModifiedDate="2009-02-02T00:00:00">
<Demographics>
<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
<TotalPurchaseYTD>0</TotalPurchaseYTD>
</IndividualSurvey>
</Demographics>
</Person.Person>
<Person.Person BusinessEntityID="679" PersonType="SC" NameStyle="0" Title="Mr." FirstName="John" MiddleName="Y." LastName="Chen" EmailPromotion="1" rowguid="D6E4BD36-001D-4286-BEC4-634E1886CBDE" ModifiedDate="2011-08-01T00:00:00">
<Demographics>
<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
<TotalPurchaseYTD>0</TotalPurchaseYTD>
</IndividualSurvey>
</Demographics>
</Person.Person>
<Person.Person BusinessEntityID="18312" PersonType="IN" NameStyle="0" FirstName="John" MiddleName="M" LastName="Clark" EmailPromotion="2" rowguid="EA8268DC-E26F-4B91-BA9E-8AA408DFE74C" ModifiedDate="2014-03-19T00:00:00">
<Demographics>
<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
<TotalPurchaseYTD>518.98</TotalPurchaseYTD>
<DateFirstPurchase>2004-04-19Z</DateFirstPurchase>
<BirthDate>1949-07-23Z</BirthDate>
<MaritalStatus>M</MaritalStatus>
<YearlyIncome>50001-75000</YearlyIncome>
<Gender>M</Gender>
<TotalChildren>2</TotalChildren>
<NumberChildrenAtHome>1</NumberChildrenAtHome>
<Education>Partial College</Education>
<Occupation>Professional</Occupation>
<HomeOwnerFlag>0</HomeOwnerFlag>
<NumberCarsOwned>0</NumberCarsOwned>
<CommuteDistance>2-5 Miles</CommuteDistance>
</IndividualSurvey>
</Demographics>
</Person.Person>
<Person.Person BusinessEntityID="723" PersonType="SC" NameStyle="0" Title="Mr." FirstName="John" MiddleName="L." LastName="Colon" EmailPromotion="0" rowguid="23030320-BA4C-47BA-A202-1D241D26EB83" ModifiedDate="2011-08-01T00:00:00">
<Demographics>
<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
<TotalPurchaseYTD>-3355.6712</TotalPurchaseYTD>
</IndividualSurvey>
</Demographics>
</Person.Person>
<Person.Person BusinessEntityID="18324" PersonType="IN" NameStyle="0" FirstName="John" MiddleName="F" LastName="Davis" EmailPromotion="1" rowguid="C61627D2-CF14-4D3C-9658-1BEC0AE90CE1" ModifiedDate="2013-12-11T00:00:00">
<Demographics>
<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
<TotalPurchaseYTD>1666</TotalPurchaseYTD>
<DateFirstPurchase>2004-01-12Z</DateFirstPurchase>
<BirthDate>1969-10-16Z</BirthDate>
<MaritalStatus>M</MaritalStatus>
<YearlyIncome>75001-100000</YearlyIncome>
<Gender>M</Gender>
<TotalChildren>2</TotalChildren>
<NumberChildrenAtHome>0</NumberChildrenAtHome>
<Education>Graduate Degree</Education>
<Occupation>Professional</Occupation>
<HomeOwnerFlag>1</HomeOwnerFlag>
<NumberCarsOwned>0</NumberCarsOwned>
<CommuteDistance>0-1 Miles</CommuteDistance>
</IndividualSurvey>
</Demographics>
</Person.Person>
<Person.Person BusinessEntityID="819" PersonType="SC" NameStyle="0" Title="Mr." FirstName="John" MiddleName="T." LastName="Donovan" Suffix="Jr." EmailPromotion="1" rowguid="EC1FB547-F0C4-457E-8998-D3ECBD5A9A2C" ModifiedDate="2011-05-31T00:00:00">
<Demographics>
<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
<TotalPurchaseYTD>-14892.8367</TotalPurchaseYTD>
</IndividualSurvey>
</Demographics>
</Person.Person>
<Person.Person BusinessEntityID="857" PersonType="SC" NameStyle="0" Title="Mr." FirstName="John" LastName="Emory" EmailPromotion="1" rowguid="46D8141F-65D3-40EA-BE3D-8A1747C27923" ModifiedDate="2013-05-30T00:00:00">
<Demographics>
<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
<TotalPurchaseYTD>-10584.318</TotalPurchaseYTD>
</IndividualSurvey>
</Demographics>
</Person.Person>
<Person.Person BusinessEntityID="873" PersonType="SC" NameStyle="0" Title="Mr." FirstName="John" LastName="Evans" EmailPromotion="0" rowguid="C1C5773F-6FBC-4636-BF03-96E804CD6A7C" ModifiedDate="2012-05-30T00:00:00">
<Demographics>
<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
<TotalPurchaseYTD>-3483.0418</TotalPurchaseYTD>
</IndividualSurvey>
</Demographics>
</Person.Person>
<Person.Person BusinessEntityID="112" PersonType="EM" NameStyle="0" FirstName="John" MiddleName="P" LastName="Evans" EmailPromotion="1" rowguid="FCFA001C-D5BC-455F-9124-21290B4A5304" ModifiedDate="2008-12-25T00:00:00">
<Demographics>
<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
<TotalPurchaseYTD>0</TotalPurchaseYTD>
</IndividualSurvey>
</Demographics>
</Person.Person>
<Person.Person BusinessEntityID="913" PersonType="SC" NameStyle="0" Title="Mr." FirstName="John" LastName="Ford" EmailPromotion="2" rowguid="7AB0363A-C2A5-46F6-8D54-49ED1237D8F2" ModifiedDate="2011-07-01T00:00:00">
<Demographics>
<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
<TotalPurchaseYTD>-5505.6174</TotalPurchaseYTD>
</IndividualSurvey>
</Demographics>
</Person.Person>
<Person.Person BusinessEntityID="905" PersonType="SC" NameStyle="0" Title="Mr." FirstName="John" LastName="Fredericksen" EmailPromotion="0" rowguid="AB39C90F-FB62-4C1E-B2BD-8378E5C306BB" ModifiedDate="2013-07-31T00:00:00">
<Demographics>
<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
<TotalPurchaseYTD>-2030.73375</TotalPurchaseYTD>
</IndividualSurvey>
</Demographics>
</Person.Person>
<Person.Person BusinessEntityID="178" PersonType="EM" NameStyle="0" FirstName="John" MiddleName="N" LastName="Frum" EmailPromotion="2" rowguid="CE13FF53-4465-4E3A-90DA-2FA0E8F19378" ModifiedDate="2009-02-24T00:00:00">
<Demographics>
<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
<TotalPurchaseYTD>0</TotalPurchaseYTD>
</IndividualSurvey>
</Demographics>
</Person.Person>
<Person.Person BusinessEntityID="18205" PersonType="IN" NameStyle="0" FirstName="John" MiddleName="M" LastName="Garcia" EmailPromotion="0" rowguid="EB954C20-77E0-4FC1-BD44-4D1BCC5FB0F1" ModifiedDate="2013-04-14T00:00:00">
<Demographics>
<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
<TotalPurchaseYTD>2434.3575</TotalPurchaseYTD>
<DateFirstPurchase>2003-05-16Z</DateFirstPurchase>
<BirthDate>1964-08-08Z</BirthDate>
<MaritalStatus>S</MaritalStatus>
<YearlyIncome>25001-50000</YearlyIncome>
<Gender>M</Gender>
<TotalChildren>2</TotalChildren>
<NumberChildrenAtHome>2</NumberChildrenAtHome>
<Education>Partial College</Education>
<Occupation>Clerical</Occupation>
<HomeOwnerFlag>1</HomeOwnerFlag>
<NumberCarsOwned>2</NumberCarsOwned>
<CommuteDistance>0-1 Miles</CommuteDistance>
</IndividualSurvey>
</Demographics>
</Person.Person>
<Person.Person BusinessEntityID="1047" PersonType="SC" NameStyle="0" Title="Mr." FirstName="John" LastName="Hanson" EmailPromotion="1" rowguid="5C96AB93-1919-4CC7-A0FA-9AC1D5FF26B0" ModifiedDate="2012-08-30T00:00:00">
<Demographics>
<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
<TotalPurchaseYTD>-3544.227</TotalPurchaseYTD>
</IndividualSurvey>
</Demographics>
</Person.Person>
<Person.Person BusinessEntityID="18357" PersonType="IN" NameStyle="0" FirstName="John" LastName="Harris" EmailPromotion="1" rowguid="E6CEBBE9-E84F-442C-9011-3AE23A9FE42A" ModifiedDate="2013-09-20T00:00:00">
<Demographics>
<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
<TotalPurchaseYTD>-45</TotalPurchaseYTD>
<DateFirstPurchase>2003-10-22Z</DateFirstPurchase>
<BirthDate>1944-04-26Z</BirthDate>
<MaritalStatus>M</MaritalStatus>
<YearlyIncome>50001-75000</YearlyIncome>
<Gender>M</Gender>
<TotalChildren>4</TotalChildren>
<NumberChildrenAtHome>0</NumberChildrenAtHome>
<Education>Bachelors </Education>
<Occupation>Management</Occupation>
<HomeOwnerFlag>1</HomeOwnerFlag>
<NumberCarsOwned>2</NumberCarsOwned>
<CommuteDistance>10+ Miles</CommuteDistance>
</IndividualSurvey>
</Demographics>
</Person.Person>
<Person.Person BusinessEntityID="18345" PersonType="IN" NameStyle="0" FirstName="John" LastName="Jackson" EmailPromotion="1" rowguid="86F5682C-FF40-48F5-AA76-741C10E4F61A" ModifiedDate="2013-09-26T00:00:00">
<Demographics>
<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
<TotalPurchaseYTD>2.7</TotalPurchaseYTD>
<DateFirstPurchase>2003-10-28Z</DateFirstPurchase>
<BirthDate>1978-03-17Z</BirthDate>
<MaritalStatus>M</MaritalStatus>
<YearlyIncome>25001-50000</YearlyIncome>
<Gender>M</Gender>
<TotalChildren>0</TotalChildren>
<NumberChildrenAtHome>0</NumberChildrenAtHome>
<Education>Partial College</Education>
<Occupation>Skilled Manual</Occupation>
<HomeOwnerFlag>1</HomeOwnerFlag>
<NumberCarsOwned>1</NumberCarsOwned>
<CommuteDistance>5-10 Miles</CommuteDistance>
</IndividualSurvey>
</Demographics>
</Person.Person>
<Person.Person BusinessEntityID="18223" PersonType="IN" NameStyle="0" FirstName="John" MiddleName="A" LastName="Johnson" EmailPromotion="0" rowguid="ADF22FBA-3349-4F53-B359-F082A2ADD550" ModifiedDate="2013-10-30T00:00:00">
<Demographics>
<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
<TotalPurchaseYTD>30.99</TotalPurchaseYTD>
<DateFirstPurchase>2003-12-01Z</DateFirstPurchase>
<BirthDate>1973-03-16Z</BirthDate>
<MaritalStatus>S</MaritalStatus>
<YearlyIncome>0-25000</YearlyIncome>
<Gender>M</Gender>
<TotalChildren>0</TotalChildren>
<NumberChildrenAtHome>0</NumberChildrenAtHome>
<Education>Partial High School</Education>
<Occupation>Manual</Occupation>
<HomeOwnerFlag>1</HomeOwnerFlag>
<NumberCarsOwned>2</NumberCarsOwned>
<CommuteDistance>1-2 Miles</CommuteDistance>
</IndividualSurvey>
</Demographics>
</Person.Person>
<Person.Person BusinessEntityID="18251" PersonType="IN" NameStyle="0" FirstName="John" LastName="Jones" EmailPromotion="0" rowguid="09A2FA2B-6F0B-4B2F-B427-0D6577DEF017" ModifiedDate="2014-04-02T00:00:00">
<Demographics>
<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
<TotalPurchaseYTD>27.69</TotalPurchaseYTD>
<DateFirstPurchase>2004-05-03Z</DateFirstPurchase>
<BirthDate>1949-09-14Z</BirthDate>
<MaritalStatus>S</MaritalStatus>
<YearlyIncome>25001-50000</YearlyIncome>
<Gender>M</Gender>
<TotalChildren>4</TotalChildren>
<NumberChildrenAtHome>0</NumberChildrenAtHome>
<Education>High School</Education>
<Occupation>Professional</Occupation>
<HomeOwnerFlag>1</HomeOwnerFlag>
<NumberCarsOwned>2</NumberCarsOwned>
<CommuteDistance>5-10 Miles</CommuteDistance>
</IndividualSurvey>
</Demographics>
</Person.Person>
<Person.Person BusinessEntityID="1197" PersonType="SC" NameStyle="0" Title="Mr." FirstName="John" LastName="Kane" EmailPromotion="1" rowguid="38A0FE20-40AB-47B9-9EEB-929D7E782E21" ModifiedDate="2013-05-30T00:00:00">
<Demographics>
<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
<TotalPurchaseYTD>-6501.528</TotalPurchaseYTD>
</IndividualSurvey>
</Demographics>
....
</Person.Person>
Stackoverflow 不允许我在正文中发布整个 XML。
我应该从 SMSS 获取 SQL 事务代码
解决方案
你应该使用nodes/value方法：
SELECT s.c.value('@FirstName', 'NVARCHAR(100)') AS FirstName,
s.c.value('@BusinessEntityID', 'INT') AS BusinessEntityID
FROM @xml.nodes('/Person.Person') s(c);
sp_xml_preparedocument/OPENXML是一种古老的解析 XML 的方法，起源于 SQL Server 2005。
如果 XML 不是格式良好的 XML，它缺少根元素，但 SQL Server 能够处理演示中提供的内容。
推荐阅读
- javascript - b-table组件中的所有列都可以启用排序吗？
- c - 为什么调用 sbrk(0) 两次会给出不同的值？
- python - Jenkins 令牌不适用于特定用户
- javascript - 在同一个函数中进行两次调用
- android - 部署 Android 应用程序包时出错
- tinkerpop - 使用属性在顶点之间添加边
- ios - 避免视图约束干扰帧更改
- c++ - 时间表之间的差距的想法
- python - 未找到关键字参数“{'pk': ''}' 的“profile_settings”的反向操作。尝试了 1 种模式：['users\\/profile\\/settings\\/(?P
[0-9]+)$'] - android - 通过更新 google-services 插件的版本来修复版本冲突