SQL server分解XML
下面是XML初始文件内容

XML code

<upd:Update xmlns:lar="http://schemas.microsoft.com/msus/2002/12/LogicalApplicabilityRules" xmlns:cmd="http://schemas.microsoft.com/msus/2002/12/UpdateHandlers/CommandLineInstallation" xmlns:bar="http://schemas.microsoft.com/msus/2002/12/BaseApplicabilityRules" xmlns:upd="http://schemas.microsoft.com/msus/2002/12/Update">    <upd:UpdateIdentity UpdateID="9f79459a-d72e-49fc-acb0-1adbb2e63686" RevisionNumber="103"/>    <upd:Properties DefaultPropertiesLanguage="en" UpdateType="Software" Handler="http://schemas.microsoft.com/msus/2002/12/UpdateHandlers/CommandLineInstallation" PublicationState="Published" CreationDate="2005-04-26T08:21:13.989Z" PublisherID="395392a0-19c0-48b7-a927-f7c15066d905" MaxDownloadSize="3320008" MinDownloadSize="0">        <upd:InstallationBehavior RebootBehavior="CanRequestReboot"/>        <upd:Language>zh-cn</upd:Language>    </upd:Properties>    <upd:LocalizedPropertiesCollection>        <upd:LocalizedProperties>            <upd:Language>en</upd:Language>            <upd:Title>Critical Update for SQL Server 2000 Desktop Engine (Windows) on Windows Server 2003 (KB829358)</upd:Title>        </upd:LocalizedProperties>    </upd:LocalizedPropertiesCollection>    <upd:Relationships>        <upd:Prerequisites>            <upd:UpdateIdentity UpdateID="30b6a809-9ba3-4c0f-9ca3-f569d01dab11"/>            <upd:AtLeastOne>                <upd:UpdateIdentity UpdateID="32b55feb-b5f5-47a7-8cc2-6a095c680267"/>                <upd:UpdateIdentity UpdateID="0d27b716-280a-42a2-864a-bcd065c162e9"/>            </upd:AtLeastOne>            <upd:UpdateIdentity UpdateID="3e0afb10-a9fb-4c16-a60e-5790c3803437"/>            <upd:AtLeastOne>                <upd:UpdateIdentity UpdateID="dbf57a08-0d5a-46ff-b30c-7715eb9498e9"/>                <upd:UpdateIdentity UpdateID="7f44c2a7-bc36-470b-be3b-c01b6dc5dd4e"/>            </upd:AtLeastOne>        </upd:Prerequisites>    </upd:Relationships>    <upd:ApplicabilityRules>        <upd:IsInstalled>            <bar:FileVersion Csidl="38" Path="Microsoft SQL Server\MSSQL$SHAREPOINT\binn\sqlservr.exe" Comparison="GreaterThanOrEqualTo" Version="2000.080.0884.00"/>        </upd:IsInstalled>        <upd:IsInstallable>            <lar:And>                <bar:FileVersion Csidl="38" Path="Microsoft SQL Server\MSSQL$SHAREPOINT\binn\sqlservr.exe" Comparison="EqualTo" Version="2000.080.0880.00"/>                <bar:WUv4RegKeyValue Data="{65657C59-23A8-4974-B8E0-BA04EBD04E4F}" Value="ProductCode" Key="HKEY_LOCAL_MACHINE" Subkey="SOFTWARE\Microsoft\Microsoft SQL Server\Sharepoint\Setup"/>                <bar:WUv4RegKeyValue Data="2052" Value="Language" Key="HKEY_LOCAL_MACHINE" Subkey="SOFTWARE\Microsoft\Microsoft SQL Server\SharePoint\MSSQLServer\CurrentVersion"/>                <lar:Not>                    <lar:Or>                        <bar:RegKeyExists Key="HKEY_LOCAL_MACHINE" Subkey="SOFTWARE\Microsoft\Microsoft SQL Server\SHAREPOINT\Tracking\Shortcuts"/>                        <bar:FileExists Csidl="38" Path="Microsoft SQL Server\MSSQL$SHAREPOINT\sqlsunin.ini"/>                        <bar:FileExists Csidl="38" Path="Microsoft SQL Server\MSSQL$SHAREPOINT\uninst.isu"/>                    </lar:Or>                </lar:Not>            </lar:And>        </upd:IsInstallable>    </upd:ApplicabilityRules>    <upd:Files>        <upd:File Digest="nBaLoGVnv7N0xq5Fs37K22/qDm4=" DigestAlgorithm="SHA1" FileName="SQL2000-KB829358-8.00.0884-CHS.exe" Size="3320008" Modified="2003-12-17T23:19:11.8170000-08:00"/>    </upd:Files>    <upd:HandlerSpecificData xsi:type="cmd:CommandLineInstallation" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">        <cmd:InstallCommand RebootByDefault="false" DefaultResult="Failed" Arguments="/Q /C:"msiexec /p sqlrun16.msp REINSTALL=ALL REINSTALLMODE=OMUS /l*v \WMSDEHotfix.log /q"" Program="SQL2000-KB829358-8.00.0884-CHS.exe">            <cmd:ReturnCode Code="3010" Reboot="true" Result="Succeeded"/>            <cmd:ReturnCode Code="0" Reboot="false" Result="Succeeded"/>        </cmd:InstallCommand>    </upd:HandlerSpecificData></upd:Update> 
 

我希望从其中抽取出某一段，结果为：

XML code

<upd:Relationships>        <upd:Prerequisites>            <upd:UpdateIdentity UpdateID="30b6a809-9ba3-4c0f-9ca3-f569d01dab11"/>            <upd:AtLeastOne>                <upd:UpdateIdentity UpdateID="32b55feb-b5f5-47a7-8cc2-6a095c680267"/>                <upd:UpdateIdentity UpdateID="0d27b716-280a-42a2-864a-bcd065c162e9"/>            </upd:AtLeastOne>            <upd:UpdateIdentity UpdateID="3e0afb10-a9fb-4c16-a60e-5790c3803437"/>            <upd:AtLeastOne>                <upd:UpdateIdentity UpdateID="dbf57a08-0d5a-46ff-b30c-7715eb9498e9"/>                <upd:UpdateIdentity UpdateID="7f44c2a7-bc36-470b-be3b-c01b6dc5dd4e"/>            </upd:AtLeastOne>        </upd:Prerequisites>    </upd:Relationships>

希望采用sp_xml_preparedocument，OPENXML，sp_xml_removedocument

或者其他方法也行

[解决办法]

SQL code

select @xml.query('declare namespace upd="http://schemas.microsoft.com/msus/2002/12/Update";//upd:Relationships')
[解决办法]
探讨


SQL code
select @xml.query('declare namespace upd="http://schemas.microsoft.com/msus/2002/12/Update";
//upd:Relationships
')

[解决办法]
SQL code
declare @idoc intdeclare @doc varchar(8000)set @doc ='<upd:Update xmlns:lar="http://schemas.microsoft.com/msus/2002/12/LogicalApplicabilityRules" xmlns:cmd="http://schemas.microsoft.com/msus/2002/12/UpdateHandlers/CommandLineInstallation" xmlns:bar="http://schemas.microsoft.com/msus/2002/12/BaseApplicabilityRules" xmlns:upd="http://schemas.microsoft.com/msus/2002/12/Update">    <upd:UpdateIdentity UpdateID="9f79459a-d72e-49fc-acb0-1adbb2e63686" RevisionNumber="103"/>    <upd:Properties DefaultPropertiesLanguage="en" UpdateType="Software" Handler="http://schemas.microsoft.com/msus/2002/12/UpdateHandlers/CommandLineInstallation" PublicationState="Published" CreationDate="2005-04-26T08:21:13.989Z" PublisherID="395392a0-19c0-48b7-a927-f7c15066d905" MaxDownloadSize="3320008" MinDownloadSize="0">        <upd:InstallationBehavior RebootBehavior="CanRequestReboot"/>        <upd:Language>zh-cn</upd:Language>    </upd:Properties>    <upd:LocalizedPropertiesCollection>        <upd:LocalizedProperties>            <upd:Language>en</upd:Language>            <upd:Title>Critical Update for SQL Server 2000 Desktop Engine (Windows) on Windows Server 2003 (KB829358)</upd:Title>        </upd:LocalizedProperties>    </upd:LocalizedPropertiesCollection>    <upd:Relationships>        <upd:Prerequisites>            <upd:UpdateIdentity UpdateID="30b6a809-9ba3-4c0f-9ca3-f569d01dab11"/>            <upd:AtLeastOne>                <upd:UpdateIdentity UpdateID="32b55feb-b5f5-47a7-8cc2-6a095c680267"/>                <upd:UpdateIdentity UpdateID="0d27b716-280a-42a2-864a-bcd065c162e9"/>            </upd:AtLeastOne>            <upd:UpdateIdentity UpdateID="3e0afb10-a9fb-4c16-a60e-5790c3803437"/>            <upd:AtLeastOne>                <upd:UpdateIdentity UpdateID="dbf57a08-0d5a-46ff-b30c-7715eb9498e9"/>                <upd:UpdateIdentity UpdateID="7f44c2a7-bc36-470b-be3b-c01b6dc5dd4e"/>            </upd:AtLeastOne>        </upd:Prerequisites>    </upd:Relationships>    <upd:ApplicabilityRules>        <upd:IsInstalled>            <bar:FileVersion Csidl="38" Path="Microsoft SQL Server\MSSQL$SHAREPOINT\binn\sqlservr.exe" Comparison="GreaterThanOrEqualTo" Version="2000.080.0884.00"/>        </upd:IsInstalled>        <upd:IsInstallable>            <lar:And>                <bar:FileVersion Csidl="38" Path="Microsoft SQL Server\MSSQL$SHAREPOINT\binn\sqlservr.exe" Comparison="EqualTo" Version="2000.080.0880.00"/>                <bar:WUv4RegKeyValue Data="{65657C59-23A8-4974-B8E0-BA04EBD04E4F}" Value="ProductCode" Key="HKEY_LOCAL_MACHINE" Subkey="SOFTWARE\Microsoft\Microsoft SQL Server\Sharepoint\Setup"/>                <bar:WUv4RegKeyValue Data="2052" Value="Language" Key="HKEY_LOCAL_MACHINE" Subkey="SOFTWARE\Microsoft\Microsoft SQL Server\SharePoint\MSSQLServer\CurrentVersion"/>                <lar:Not>                    <lar:Or>                        <bar:RegKeyExists Key="HKEY_LOCAL_MACHINE" Subkey="SOFTWARE\Microsoft\Microsoft SQL Server\SHAREPOINT\Tracking\Shortcuts"/>                        <bar:FileExists Csidl="38" Path="Microsoft SQL Server\MSSQL$SHAREPOINT\sqlsunin.ini"/>                        <bar:FileExists Csidl="38" Path="Microsoft SQL Server\MSSQL$SHAREPOINT\uninst.isu"/>                    </lar:Or>                </lar:Not>            </lar:And>        </upd:IsInstallable>    </upd:ApplicabilityRules>    <upd:Files>        <upd:File Digest="nBaLoGVnv7N0xq5Fs37K22/qDm4=" DigestAlgorithm="SHA1" FileName="SQL2000-KB829358-8.00.0884-CHS.exe" Size="3320008" Modified="2003-12-17T23:19:11.8170000-08:00"/>    </upd:Files>    <upd:HandlerSpecificData xsi:type="cmd:CommandLineInstallation" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">        <cmd:InstallCommand RebootByDefault="false" DefaultResult="Failed" Arguments="/Q /C:"msiexec /p sqlrun16.msp REINSTALL=ALL REINSTALLMODE=OMUS /l*v \WMSDEHotfix.log /q"" Program="SQL2000-KB829358-8.00.0884-CHS.exe">            <cmd:ReturnCode Code="3010" Reboot="true" Result="Succeeded"/>            <cmd:ReturnCode Code="0" Reboot="false" Result="Succeeded"/>        </cmd:InstallCommand>    </upd:HandlerSpecificData></upd:Update>'EXEC sp_xml_preparedocument @idoc OUTPUT, @doc, '<q xmlns:upd="http://schemas.microsoft.com/msus/2002/12/Update"/>'--SELECT * FROM OPENXML (@idoc, '/upd:Update/upd:Relationships')      WITH (            comment ntext '@mp:xmltext')SELECT    * FROM       OPENXML (@idoc, '/upd:Update/upd:Relationships') WITH (Col1 ntext '@mp:xmltext')EXEC sp_xml_removedocument   @idoc 
 
[解决办法]
路过 学习
[解决办法]
学习学习呀
[解决办法]
学习了，以后留着用。
[解决办法]
学习xquery！
[解决办法]
thank you
[解决办法]

学习!!!!!!!!
[解决办法]
学习啊！
[解决办法]
每天回帖即可获得10分可用分！小技巧 呵呵
[解决办法]
发动机是公平倒萨
[解决办法]
高的分数达
[解决办法]
学习学习
[解决办法]
学习了。。
[解决办法]
帮顶...学习...
[解决办法]
学习了。
[解决办法]
顶，顶，顶。
[解决办法]
波大精深
[解决办法]
马一下吧
[解决办法]
学习来的
[解决办法]
学习学习
[解决办法]
学习学习  
  
[解决办法]
学习学习学习
[解决办法]
顶，学习了
[解决办法]
一点都看不懂。。这行好难混···
[解决办法]
路过，，
[解决办法]
学习学习学习
[解决办法]
还是看不懂。。
[解决办法]

[解决办法]
路过学习下先
[解决办法]
要顶要顶要顶要顶
[解决办法]
看过回帖，顶。
[解决办法]
学习了
[解决办法]
我想弱弱的问下楼主，你是把XML数据存储在哪里的？是在SQL 2005作为XML类型列存储的吗？谢谢@
[解决办法]
nx hh!!