登录
当前位置： 首页 > 知识库问答 >
问题：

SQL Server 2005-查询复杂XML（SOAP响应）

桑坚
2023-03-14

我想使用SQL服务器查询XML文件，以将以下XML示例作为平面表：

<?xml version="1.0" ?> 
  <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <soapenv:Body>
  <ns1:getArtifactDetailListResponse soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xmlns:ns1="http://schema.open.collab.net/sfee50/soap60/service">
  <getArtifactDetailListReturn href="#id0" /> 
  </ns1:getArtifactDetailListResponse>
  <multiRef id="id0" soapenc:root="0" soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xsi:type="ns2:ArtifactDetailSoapList" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/" xmlns:ns2="http://schema.open.collab.net/sfee50/soap60/type">
  <dataRows soapenc:arrayType="ns2:ArtifactDetailSoapRow[4]" xsi:type="soapenc:Array">
  <dataRows href="#id1" /> 
  <dataRows href="#id2" /> 
  <dataRows href="#id3" /> 
  <dataRows href="#id4" /> 
  </dataRows>
  <fullListSize xsi:type="xsd:int">4</fullListSize> 
  </multiRef>
  <multiRef id="id2" soapenc:root="0" soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xsi:type="ns3:ArtifactDetailSoapRow" xmlns:ns3="http://schema.open.collab.net/sfee50/soap60/type" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/">
  <actualEffort xsi:type="xsd:int">0</actualEffort> 
  <artifactGroup xsi:type="xsd:string" /> 
  <assignedToFullname xsi:type="xsd:string">Anon User</assignedToFullname> 
  <assignedToUsername xsi:type="xsd:string">anon_user</assignedToUsername> 
  <autosumming xsi:type="xsd:boolean">false</autosumming> 
  <category xsi:type="xsd:string">Internal Improvement</category> 
  <closeDate xsi:type="xsd:dateTime" xsi:nil="true" /> 
  <customer xsi:type="xsd:string" /> 
  <description xsi:type="xsd:string">Description Here</description> 
  <estimatedEffort xsi:type="xsd:int">0</estimatedEffort> 
  <flexFields href="#id5" /> 
  <folderId xsi:type="xsd:string">tracker15657</folderId> 
  <folderPathString xsi:type="xsd:string">tracker.brd_new_request</folderPathString> 
  <folderTitle xsi:type="xsd:string">BRDs (New/Change Requests)</folderTitle> 
  <id xsi:type="xsd:string">artf705037</id> 
  <lastModifiedDate xsi:type="xsd:dateTime">2013-02-01T10:43:02.000Z</lastModifiedDate> 
  <planningFolderId xsi:type="xsd:string" xsi:nil="true" /> 
  <planningFolderTitle xsi:type="xsd:string" xsi:nil="true" /> 
  <points xsi:type="xsd:int">0</points> 
  <priority xsi:type="xsd:int">4</priority> 
  <projectId xsi:type="xsd:string">proj7173</projectId> 
  <projectPathString xsi:type="xsd:string">projects.rapid_application_developmet</projectPathString> 
  <projectTitle xsi:type="xsd:string">Rapid Application Development</projectTitle> 
  <remainingEffort xsi:type="xsd:int">0</remainingEffort> 
  <reportedInReleaseId xsi:type="xsd:string" xsi:nil="true" /> 
  <reportedInReleaseTitle xsi:type="xsd:string" xsi:nil="true" /> 
  <resolvedInReleaseId xsi:type="xsd:string" xsi:nil="true" /> 
  <resolvedInReleaseTitle xsi:type="xsd:string" xsi:nil="true" /> 
  <status xsi:type="xsd:string">RAD Reviewing</status> 
  <statusClass xsi:type="xsd:string">Open</statusClass> 
  <submittedByFullname xsi:type="xsd:string">Anon User</submittedByFullname> 
  <submittedByUsername xsi:type="xsd:string">anon_user</submittedByUsername> 
  <submittedDate xsi:type="xsd:dateTime">2013-02-01T10:43:02.000Z</submittedDate> 
  <title xsi:type="xsd:string">SWAPS - Weekly Tracer Exception Report</title> 
  <trackerIcon xsi:type="xsd:string" xsi:nil="true" /> 
  <version xsi:type="xsd:int">100</version> 
  </multiRef>
  <multiRef id="id1" soapenc:root="0" soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xsi:type="ns4:ArtifactDetailSoapRow" xmlns:ns4="http://schema.open.collab.net/sfee50/soap60/type" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/">
  <actualEffort xsi:type="xsd:int">0</actualEffort> 
  <artifactGroup xsi:type="xsd:string" /> 
  <assignedToFullname xsi:type="xsd:string">Anon User</assignedToFullname> 
  <assignedToUsername xsi:type="xsd:string">anon_user</assignedToUsername> 
  <autosumming xsi:type="xsd:boolean">false</autosumming> 
  <category xsi:type="xsd:string">Internal Improvement</category> 
  <closeDate xsi:type="xsd:dateTime" xsi:nil="true" /> 
  <customer xsi:type="xsd:string" /> 
  <description xsi:type="xsd:string">KOP embelished for internal improvement/automation.</description> 
  <estimatedEffort xsi:type="xsd:int">0</estimatedEffort> 
  <flexFields href="#id6" /> 
  <folderId xsi:type="xsd:string">tracker15657</folderId> 
  <folderPathString xsi:type="xsd:string">tracker.brd_new_request</folderPathString> 
  <folderTitle xsi:type="xsd:string">BRDs (New/Change Requests)</folderTitle> 
  <id xsi:type="xsd:string">artf705038</id> 
  <lastModifiedDate xsi:type="xsd:dateTime">2013-02-01T10:49:14.000Z</lastModifiedDate> 
  <planningFolderId xsi:type="xsd:string" xsi:nil="true" /> 
  <planningFolderTitle xsi:type="xsd:string" xsi:nil="true" /> 
  <points xsi:type="xsd:int">0</points> 
  <priority xsi:type="xsd:int">4</priority> 
  <projectId xsi:type="xsd:string">proj7173</projectId> 
  <projectPathString xsi:type="xsd:string">projects.rapid_application_developmet</projectPathString> 
  <projectTitle xsi:type="xsd:string">Rapid Application Development</projectTitle> 
  <remainingEffort xsi:type="xsd:int">0</remainingEffort> 
  <reportedInReleaseId xsi:type="xsd:string" xsi:nil="true" /> 
  <reportedInReleaseTitle xsi:type="xsd:string" xsi:nil="true" /> 
  <resolvedInReleaseId xsi:type="xsd:string" xsi:nil="true" /> 
  <resolvedInReleaseTitle xsi:type="xsd:string" xsi:nil="true" /> 
  <status xsi:type="xsd:string">RAD Reviewing</status> 
  <statusClass xsi:type="xsd:string">Open</statusClass> 
  <submittedByFullname xsi:type="xsd:string">Anon User</submittedByFullname> 
  <submittedByUsername xsi:type="xsd:string">anon_user</submittedByUsername> 
  <submittedDate xsi:type="xsd:dateTime">2013-02-01T10:49:14.000Z</submittedDate> 
  <title xsi:type="xsd:string">SWAPS   Weekly Trade Support Report</title> 
  <trackerIcon xsi:type="xsd:string" xsi:nil="true" /> 
  <version xsi:type="xsd:int">100</version> 
  </multiRef>
  <multiRef id="id4" soapenc:root="0" soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xsi:type="ns5:ArtifactDetailSoapRow" xmlns:ns5="http://schema.open.collab.net/sfee50/soap60/type" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/">
  <actualEffort xsi:type="xsd:int">0</actualEffort> 
  <artifactGroup xsi:type="xsd:string" /> 
  <assignedToFullname xsi:type="xsd:string">Anon User</assignedToFullname> 
  <assignedToUsername xsi:type="xsd:string">anon_user</assignedToUsername> 
  <autosumming xsi:type="xsd:boolean">false</autosumming> 
  <category xsi:type="xsd:string">Internal Improvement</category> 
  <closeDate xsi:type="xsd:dateTime" xsi:nil="true" /> 
  <customer xsi:type="xsd:string" /> 
  <description xsi:type="xsd:string">BRD submitted for internal automation of BAU.</description> 
  <estimatedEffort xsi:type="xsd:int">0</estimatedEffort> 
  <flexFields href="#id7" /> 
  <folderId xsi:type="xsd:string">tracker15657</folderId> 
  <folderPathString xsi:type="xsd:string">tracker.brd_new_request</folderPathString> 
  <folderTitle xsi:type="xsd:string">BRDs (New/Change Requests)</folderTitle> 
  <id xsi:type="xsd:string">artf704445</id> 
  <lastModifiedDate xsi:type="xsd:dateTime">2013-01-30T16:31:35.000Z</lastModifiedDate> 
  <planningFolderId xsi:type="xsd:string" xsi:nil="true" /> 
  <planningFolderTitle xsi:type="xsd:string" xsi:nil="true" /> 
  <points xsi:type="xsd:int">0</points> 
  <priority xsi:type="xsd:int">4</priority> 
  <projectId xsi:type="xsd:string">proj7173</projectId> 
  <projectPathString xsi:type="xsd:string">projects.rapid_application_developmet</projectPathString> 
  <projectTitle xsi:type="xsd:string">Rapid Application Development</projectTitle> 
  <remainingEffort xsi:type="xsd:int">0</remainingEffort> 
  <reportedInReleaseId xsi:type="xsd:string" xsi:nil="true" /> 
  <reportedInReleaseTitle xsi:type="xsd:string" xsi:nil="true" /> 
  <resolvedInReleaseId xsi:type="xsd:string" xsi:nil="true" /> 
  <resolvedInReleaseTitle xsi:type="xsd:string" xsi:nil="true" /> 
  <status xsi:type="xsd:string">RAD Reviewing</status> 
  <statusClass xsi:type="xsd:string">Open</statusClass> 
  <submittedByFullname xsi:type="xsd:string">Anon User</submittedByFullname> 
  <submittedByUsername xsi:type="xsd:string">anon_user</submittedByUsername> 
  <submittedDate xsi:type="xsd:dateTime">2013-01-30T16:29:02.000Z</submittedDate> 
  <title xsi:type="xsd:string">SWAPS Daily MIS Overall Performance</title> 
  <trackerIcon xsi:type="xsd:string" xsi:nil="true" /> 
  <version xsi:type="xsd:int">101</version> 
  </multiRef>
  <multiRef id="id3" soapenc:root="0" soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xsi:type="ns6:ArtifactDetailSoapRow" xmlns:ns6="http://schema.open.collab.net/sfee50/soap60/type" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/">
  <actualEffort xsi:type="xsd:int">0</actualEffort> 
  <artifactGroup xsi:type="xsd:string" /> 
  <assignedToFullname xsi:type="xsd:string">Anon User</assignedToFullname> 
  <assignedToUsername xsi:type="xsd:string">anon_user</assignedToUsername> 
  <autosumming xsi:type="xsd:boolean">false</autosumming> 
  <category xsi:type="xsd:string">Internal Improvement</category> 
  <closeDate xsi:type="xsd:dateTime" xsi:nil="true" /> 
  <customer xsi:type="xsd:string" /> 
  <description xsi:type="xsd:string">BRD submitted for internal automation of BAU.</description> 
  <estimatedEffort xsi:type="xsd:int">0</estimatedEffort> 
  <flexFields href="#id8" /> 
  <folderId xsi:type="xsd:string">tracker15657</folderId> 
  <folderPathString xsi:type="xsd:string">tracker.brd_new_request</folderPathString> 
  <folderTitle xsi:type="xsd:string">BRDs (New/Change Requests)</folderTitle> 
  <id xsi:type="xsd:string">artf704570</id> 
  <lastModifiedDate xsi:type="xsd:dateTime">2013-01-31T10:27:13.000Z</lastModifiedDate> 
  <planningFolderId xsi:type="xsd:string" xsi:nil="true" /> 
  <planningFolderTitle xsi:type="xsd:string" xsi:nil="true" /> 
  <points xsi:type="xsd:int">0</points> 
  <priority xsi:type="xsd:int">4</priority> 
  <projectId xsi:type="xsd:string">proj7173</projectId> 
  <projectPathString xsi:type="xsd:string">projects.rapid_application_developmet</projectPathString> 
  <projectTitle xsi:type="xsd:string">Rapid Application Development</projectTitle> 
  <remainingEffort xsi:type="xsd:int">0</remainingEffort> 
  <reportedInReleaseId xsi:type="xsd:string" xsi:nil="true" /> 
  <reportedInReleaseTitle xsi:type="xsd:string" xsi:nil="true" /> 
  <resolvedInReleaseId xsi:type="xsd:string" xsi:nil="true" /> 
  <resolvedInReleaseTitle xsi:type="xsd:string" xsi:nil="true" /> 
  <status xsi:type="xsd:string">RAD Reviewing</status> 
  <statusClass xsi:type="xsd:string">Open</statusClass> 
  <submittedByFullname xsi:type="xsd:string">Anon User</submittedByFullname> 
  <submittedByUsername xsi:type="xsd:string">anon_user</submittedByUsername> 
  <submittedDate xsi:type="xsd:dateTime">2013-01-31T10:27:13.000Z</submittedDate> 
  <title xsi:type="xsd:string">SWAPS Heatmap</title> 
  <trackerIcon xsi:type="xsd:string" xsi:nil="true" /> 
  <version xsi:type="xsd:int">100</version> 
  </multiRef>
  <multiRef id="id7" soapenc:root="0" soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xsi:type="ns7:SoapFieldValues" xmlns:ns7="http://schema.open.collab.net/sfee50/soap60/type" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/">
  <names soapenc:arrayType="xsd:string[14]" xsi:type="soapenc:Array">
  <names xsi:type="xsd:string">BRD Received</names> 
  <names xsi:type="xsd:string">Dev Started</names> 
  <names xsi:type="xsd:string">EUDA Registered</names> 
  <names xsi:type="xsd:string">Prioritised   Pending Dev</names> 
  <names xsi:type="xsd:string">Product</names> 
  <names xsi:type="xsd:string">Product Group</names> 
  <names xsi:type="xsd:string">Requestor POC</names> 
  <names xsi:type="xsd:string">Signed Off</names> 
  <names xsi:type="xsd:string">UAT Delivered</names> 
  <names xsi:type="xsd:string">UAT Delivery Date</names> 
  <names xsi:type="xsd:string">VP Approval</names> 
  <names xsi:type="xsd:string">BMSC POC</names> 
  <names xsi:type="xsd:string">$fild306322$Coverage</names> 
  <names xsi:type="xsd:string">$fild306551$test</names> 
  </names>
  <types soapenc:arrayType="xsd:string[14]" xsi:type="soapenc:Array">
  <types xsi:type="xsd:string">Date</types> 
  <types xsi:type="xsd:string">Date</types> 
  <types xsi:type="xsd:string">Date</types> 
  <types xsi:type="xsd:string">Date</types> 
  <types xsi:type="xsd:string">String</types> 
  <types xsi:type="xsd:string">String</types> 
  <types xsi:type="xsd:string">String</types> 
  <types xsi:type="xsd:string">Date</types> 
  <types xsi:type="xsd:string">Date</types> 
  <types xsi:type="xsd:string">Date</types> 
  <types xsi:type="xsd:string">Date</types> 
  <types xsi:type="xsd:string">User</types> 
  <types xsi:type="xsd:string">User</types> 
  <types xsi:type="xsd:string">User</types> 
  </types>
  <values soapenc:arrayType="xsd:anyType[14]" xsi:type="soapenc:Array">
  <values xsi:type="xsd:dateTime">2013-01-30T08:00:00.000Z</values> 
  <values xsi:type="xsd:anyType" xsi:nil="true" /> 
  <values xsi:type="xsd:anyType" xsi:nil="true" /> 
  <values xsi:type="xsd:anyType" xsi:nil="true" /> 
  <values xsi:type="soapenc:string">Synthetics</values> 
  <values xsi:type="soapenc:string">GPF</values> 
  <values xsi:type="soapenc:string" /> 
  <values xsi:type="xsd:anyType" xsi:nil="true" /> 
  <values xsi:type="xsd:anyType" xsi:nil="true" /> 
  <values xsi:type="xsd:anyType" xsi:nil="true" /> 
  <values xsi:type="xsd:dateTime">2013-01-30T08:00:00.000Z</values> 
  <values xsi:type="soapenc:string">anon_user</values> 
  <values xsi:type="xsd:anyType" xsi:nil="true" /> 
  <values xsi:type="xsd:anyType" xsi:nil="true" /> 
  </values>
  </multiRef>
  <multiRef id="id8" soapenc:root="0" soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xsi:type="ns8:SoapFieldValues" xmlns:ns8="http://schema.open.collab.net/sfee50/soap60/type" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/">
  <names soapenc:arrayType="xsd:string[14]" xsi:type="soapenc:Array">
  <names xsi:type="xsd:string">BRD Received</names> 
  <names xsi:type="xsd:string">Dev Started</names> 
  <names xsi:type="xsd:string">EUDA Registered</names> 
  <names xsi:type="xsd:string">Prioritised   Pending Dev</names> 
  <names xsi:type="xsd:string">Product</names> 
  <names xsi:type="xsd:string">Product Group</names> 
  <names xsi:type="xsd:string">Requestor POC</names> 
  <names xsi:type="xsd:string">Signed Off</names> 
  <names xsi:type="xsd:string">UAT Delivered</names> 
  <names xsi:type="xsd:string">UAT Delivery Date</names> 
  <names xsi:type="xsd:string">VP Approval</names> 
  <names xsi:type="xsd:string">BMSC POC</names> 
  <names xsi:type="xsd:string">$fild306322$Coverage</names> 
  <names xsi:type="xsd:string">$fild306551$test</names> 
  </names>
  <types soapenc:arrayType="xsd:string[14]" xsi:type="soapenc:Array">
  <types xsi:type="xsd:string">Date</types> 
  <types xsi:type="xsd:string">Date</types> 
  <types xsi:type="xsd:string">Date</types> 
  <types xsi:type="xsd:string">Date</types> 
  <types xsi:type="xsd:string">String</types> 
  <types xsi:type="xsd:string">String</types> 
  <types xsi:type="xsd:string">String</types> 
  <types xsi:type="xsd:string">Date</types> 
  <types xsi:type="xsd:string">Date</types> 
  <types xsi:type="xsd:string">Date</types> 
  <types xsi:type="xsd:string">Date</types> 
  <types xsi:type="xsd:string">User</types> 
  <types xsi:type="xsd:string">User</types> 
  <types xsi:type="xsd:string">User</types> 
  </types>
  <values soapenc:arrayType="xsd:anyType[14]" xsi:type="soapenc:Array">
  <values xsi:type="xsd:dateTime">2013-01-31T08:00:00.000Z</values> 
  <values xsi:type="xsd:anyType" xsi:nil="true" /> 
  <values xsi:type="xsd:anyType" xsi:nil="true" /> 
  <values xsi:type="xsd:anyType" xsi:nil="true" /> 
  <values xsi:type="soapenc:string">Synthetics</values> 
  <values xsi:type="soapenc:string">GPF</values> 
  <values xsi:type="soapenc:string" /> 
  <values xsi:type="xsd:anyType" xsi:nil="true" /> 
  <values xsi:type="xsd:anyType" xsi:nil="true" /> 
  <values xsi:type="xsd:anyType" xsi:nil="true" /> 
  <values xsi:type="xsd:dateTime">2013-01-31T08:00:00.000Z</values> 
  <values xsi:type="soapenc:string">anon_user</values> 
  <values xsi:type="xsd:anyType" xsi:nil="true" /> 
  <values xsi:type="xsd:anyType" xsi:nil="true" /> 
  </values>
  </multiRef>
  <multiRef id="id6" soapenc:root="0" soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xsi:type="ns9:SoapFieldValues" xmlns:ns9="http://schema.open.collab.net/sfee50/soap60/type" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/">
  <names soapenc:arrayType="xsd:string[14]" xsi:type="soapenc:Array">
  <names xsi:type="xsd:string">BRD Received</names> 
  <names xsi:type="xsd:string">Dev Started</names> 
  <names xsi:type="xsd:string">EUDA Registered</names> 
  <names xsi:type="xsd:string">Prioritised   Pending Dev</names> 
  <names xsi:type="xsd:string">Product</names> 
  <names xsi:type="xsd:string">Product Group</names> 
  <names xsi:type="xsd:string">Requestor POC</names> 
  <names xsi:type="xsd:string">Signed Off</names> 
  <names xsi:type="xsd:string">UAT Delivered</names> 
  <names xsi:type="xsd:string">UAT Delivery Date</names> 
  <names xsi:type="xsd:string">VP Approval</names> 
  <names xsi:type="xsd:string">BMSC POC</names> 
  <names xsi:type="xsd:string">$fild306322$Coverage</names> 
  <names xsi:type="xsd:string">$fild306551$test</names> 
  </names>
  <types soapenc:arrayType="xsd:string[14]" xsi:type="soapenc:Array">
  <types xsi:type="xsd:string">Date</types> 
  <types xsi:type="xsd:string">Date</types> 
  <types xsi:type="xsd:string">Date</types> 
  <types xsi:type="xsd:string">Date</types> 
  <types xsi:type="xsd:string">String</types> 
  <types xsi:type="xsd:string">String</types> 
  <types xsi:type="xsd:string">String</types> 
  <types xsi:type="xsd:string">Date</types> 
  <types xsi:type="xsd:string">Date</types> 
  <types xsi:type="xsd:string">Date</types> 
  <types xsi:type="xsd:string">Date</types> 
  <types xsi:type="xsd:string">User</types> 
  <types xsi:type="xsd:string">User</types> 
  <types xsi:type="xsd:string">User</types> 
  </types>
  <values soapenc:arrayType="xsd:anyType[14]" xsi:type="soapenc:Array">
  <values xsi:type="xsd:dateTime">2013-02-01T08:00:00.000Z</values> 
  <values xsi:type="xsd:anyType" xsi:nil="true" /> 
  <values xsi:type="xsd:anyType" xsi:nil="true" /> 
  <values xsi:type="xsd:anyType" xsi:nil="true" /> 
  <values xsi:type="soapenc:string">Synthetics</values> 
  <values xsi:type="soapenc:string">GPF</values> 
  <values xsi:type="soapenc:string" /> 
  <values xsi:type="xsd:anyType" xsi:nil="true" /> 
  <values xsi:type="xsd:anyType" xsi:nil="true" /> 
  <values xsi:type="xsd:anyType" xsi:nil="true" /> 
  <values xsi:type="xsd:dateTime">2013-02-01T08:00:00.000Z</values> 
  <values xsi:type="soapenc:string">anon_user</values> 
  <values xsi:type="xsd:anyType" xsi:nil="true" /> 
  <values xsi:type="xsd:anyType" xsi:nil="true" /> 
  </values>
  </multiRef>
  <multiRef id="id5" soapenc:root="0" soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xsi:type="ns10:SoapFieldValues" xmlns:ns10="http://schema.open.collab.net/sfee50/soap60/type" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/">
  <names soapenc:arrayType="xsd:string[14]" xsi:type="soapenc:Array">
  <names xsi:type="xsd:string">BRD Received</names> 
  <names xsi:type="xsd:string">Dev Started</names> 
  <names xsi:type="xsd:string">EUDA Registered</names> 
  <names xsi:type="xsd:string">Prioritised   Pending Dev</names> 
  <names xsi:type="xsd:string">Product</names> 
  <names xsi:type="xsd:string">Product Group</names> 
  <names xsi:type="xsd:string">Requestor POC</names> 
  <names xsi:type="xsd:string">Signed Off</names> 
  <names xsi:type="xsd:string">UAT Delivered</names> 
  <names xsi:type="xsd:string">UAT Delivery Date</names> 
  <names xsi:type="xsd:string">VP Approval</names> 
  <names xsi:type="xsd:string">BMSC POC</names> 
  <names xsi:type="xsd:string">$fild306322$Coverage</names> 
  <names xsi:type="xsd:string">$fild306551$test</names> 
  </names>
  <types soapenc:arrayType="xsd:string[14]" xsi:type="soapenc:Array">
  <types xsi:type="xsd:string">Date</types> 
  <types xsi:type="xsd:string">Date</types> 
  <types xsi:type="xsd:string">Date</types> 
  <types xsi:type="xsd:string">Date</types> 
  <types xsi:type="xsd:string">String</types> 
  <types xsi:type="xsd:string">String</types> 
  <types xsi:type="xsd:string">String</types> 
  <types xsi:type="xsd:string">Date</types> 
  <types xsi:type="xsd:string">Date</types> 
  <types xsi:type="xsd:string">Date</types> 
  <types xsi:type="xsd:string">Date</types> 
  <types xsi:type="xsd:string">User</types> 
  <types xsi:type="xsd:string">User</types> 
  <types xsi:type="xsd:string">User</types> 
  </types>
  <values soapenc:arrayType="xsd:anyType[14]" xsi:type="soapenc:Array">
  <values xsi:type="xsd:dateTime">2013-02-01T08:00:00.000Z</values> 
  <values xsi:type="xsd:anyType" xsi:nil="true" /> 
  <values xsi:type="xsd:anyType" xsi:nil="true" /> 
  <values xsi:type="xsd:anyType" xsi:nil="true" /> 
  <values xsi:type="soapenc:string">Synthetics</values> 
  <values xsi:type="soapenc:string">GPF</values> 
  <values xsi:type="soapenc:string" /> 
  <values xsi:type="xsd:anyType" xsi:nil="true" /> 
  <values xsi:type="xsd:anyType" xsi:nil="true" /> 
  <values xsi:type="xsd:anyType" xsi:nil="true" /> 
  <values xsi:type="xsd:dateTime">2013-02-01T08:00:00.000Z</values> 
  <values xsi:type="soapenc:string">anon_user</values> 
  <values xsi:type="xsd:anyType" xsi:nil="true" /> 
  <values xsi:type="xsd:anyType" xsi:nil="true" /> 
  </values>
  </multiRef>
  </soapenv:Body>
  </soapenv:Envelope>

所有这些仅代表来自TeamForge CollabNet站点的4条记录。

我可以看到这样的“结构”。路径soapenv:Envelope/soapenv:Body/multiRef/dataRows包含对奇数格式的4条记录的4个引用

然后使用href id，您将转到这样的路径：

/soapenv:Envelope/soapenv:Body/multiRef (where the id attribute matches the record)

在这个节点上，我希望能够选择一些元素，例如“assignedToFullname”、“title”等。

然而，在这个节点中有一个名为“flexFields”的元素，看起来像这样：

<flexFields href="#id6" />

这是因为在CollabNet中，您可以有许多自定义字段。因此遵循与之前相同的规则，flexfield信息位于：

/soapenv:Envelope/soapenv:Body/multiRef (where the id attribute matches the flexfield href)

一条记录的XML片段如下所示：

<multiRef id="id7" soapenc:root="0" soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xsi:type="ns7:SoapFieldValues" xmlns:ns7="http://schema.open.collab.net/sfee50/soap60/type" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/">
  <names soapenc:arrayType="xsd:string[14]" xsi:type="soapenc:Array">
  <names xsi:type="xsd:string">BRD Received</names> 
  <names xsi:type="xsd:string">Dev Started</names> 
  <names xsi:type="xsd:string">EUDA Registered</names> 
  <names xsi:type="xsd:string">Prioritised   Pending Dev</names> 
  <names xsi:type="xsd:string">Product</names> 
  <names xsi:type="xsd:string">Product Group</names> 
  <names xsi:type="xsd:string">Requestor POC</names> 
  <names xsi:type="xsd:string">Signed Off</names> 
  <names xsi:type="xsd:string">UAT Delivered</names> 
  <names xsi:type="xsd:string">UAT Delivery Date</names> 
  <names xsi:type="xsd:string">VP Approval</names> 
  <names xsi:type="xsd:string">BMSC POC</names> 
  <names xsi:type="xsd:string">$fild306322$Coverage</names> 
  <names xsi:type="xsd:string">$fild306551$test</names> 
  </names>
  <types soapenc:arrayType="xsd:string[14]" xsi:type="soapenc:Array">
  <types xsi:type="xsd:string">Date</types> 
  <types xsi:type="xsd:string">Date</types> 
  <types xsi:type="xsd:string">Date</types> 
  <types xsi:type="xsd:string">Date</types> 
  <types xsi:type="xsd:string">String</types> 
  <types xsi:type="xsd:string">String</types> 
  <types xsi:type="xsd:string">String</types> 
  <types xsi:type="xsd:string">Date</types> 
  <types xsi:type="xsd:string">Date</types> 
  <types xsi:type="xsd:string">Date</types> 
  <types xsi:type="xsd:string">Date</types> 
  <types xsi:type="xsd:string">User</types> 
  <types xsi:type="xsd:string">User</types> 
  <types xsi:type="xsd:string">User</types> 
  </types>
  <values soapenc:arrayType="xsd:anyType[14]" xsi:type="soapenc:Array">
  <values xsi:type="xsd:dateTime">2013-01-30T08:00:00.000Z</values> 
  <values xsi:type="xsd:anyType" xsi:nil="true" /> 
  <values xsi:type="xsd:anyType" xsi:nil="true" /> 
  <values xsi:type="xsd:anyType" xsi:nil="true" /> 
  <values xsi:type="soapenc:string">Synthetics</values> 
  <values xsi:type="soapenc:string">GPF</values> 
  <values xsi:type="soapenc:string" /> 
  <values xsi:type="xsd:anyType" xsi:nil="true" /> 
  <values xsi:type="xsd:anyType" xsi:nil="true" /> 
  <values xsi:type="xsd:anyType" xsi:nil="true" /> 
  <values xsi:type="xsd:dateTime">2013-01-30T08:00:00.000Z</values> 
  <values xsi:type="soapenc:string">anon_user</values> 
  <values xsi:type="xsd:anyType" xsi:nil="true" /> 
  <values xsi:type="xsd:anyType" xsi:nil="true" /> 
  </values>
  </multiRef>

是否可以使用SQL Server将其输出到一个表中，其中包含许多指定的元素作为列，再加上fledFields及其值作为列。

在此方面的任何帮助都将不胜感激。

更新OK到目前为止，我已经成功地从XML文件中获取了正常数据，如下所示：

SELECT T.c.value('@id','varchar(5)') as [id]
    , T.c.value('title[1]','varchar(255)') as [Title]
    , T.c.value('id[1]','varchar(255)') as [Artifact]
    , T.c.value('priority[1]','varchar(255)') as [Priority]
    , T.c.value('assignedToFullname[1]','varchar(255)') as [Assignee]
    , T.c.value('status[1]','varchar(255)') as [Status]
    , T.c.value('statusClass[1]','varchar(255)') as [statusClass]
    , T.c.value('folderTitle[1]','varchar(255)') as [Tracker]
    , Replace(T.c.value('flexFields[1]/@href','varchar(255)'),'#','') as [flexFieldsID]
FROM @xml.nodes('declare namespace soapenv="http://schemas.xmlsoap.org/soap/envelope/";
/soapenv:Envelope[1]/soapenv:Body[1]/multiRef') T(c)
WHERE T.c.value('@id','varchar(5)') IN
    (SELECT Replace(DR.h.value('@href','varchar(5)'),'#','') as [DataRowID]
    FROM @xml.nodes('declare namespace soapenv="http://schemas.xmlsoap.org/soap/envelope/";
    /soapenv:Envelope[1]/soapenv:Body[1]/multiRef/dataRows/dataRows') DR(h))

然而，这看起来很可怕，我想知道是否有人知道更优雅的方法。我还在如何将“flexFields”附加到这个结果表的末尾。

进一步更新我想我已经很接近了，但我不知道如何将flexFields放入一个“平面”表中，因为“名称”和“值”之间没有结构上的联系，它只是根据它的外观在顺序上进行匹配，所以第一个名称是第一个值的字段名，等等，我只是不知道如何编写查询来实现这一点。

共有1个答案

萧浩漫
2023-03-14

SQL Server提供了OPENXML方法来从XML中提取数据，这些数据可以插入到任何表或临时表中。示例：

    DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
   <Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
      <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
      <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
   </Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
   <Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
      <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT    *
FROM       OPENXML (@idoc, '/ROOT/Customer',1)
            WITH (CustomerID  varchar(10),
                  ContactName varchar(20))

有关详细信息，请参考：OPENXML

我在这种方法中看到的唯一问题是，示例XML中有很多转义/无效字符用于读取XML。为此，您必须使用replace to some valid character替换中间PL/SQL中的XML，以便将值从XML提取到表中。很抱歉，回复不完整，我现在有点忙，无法提供实际的工作查询。希望有帮助。

 类似资料：
  • Linq复杂查询C#

    我有这个： 如何从hashlist中排除“item”？打破了我的头。Linq不想对我开放。

  • @ManyToMany JPA 2复杂查询

    问题内容： 我有以下ManyToMany映射。 我想检索与Classe2实体有关系的所有Class1实体，其中class2Id = 1和class2Id = 2和class2Id = 3。{1,2,3} 或者，要过滤在其class2列表上具有的Classe1实体，请使用具有以下值的Class2实体：class2Id = 1和class2Id = 2和class2Id = 3 例如： 如果在联接表上

  • Java REST API复杂查询

    我有一张这样的桌子： 现在我想创建一个返回经过过滤的数据集的REST APIendpoint： 它应正确筛选API参数的任何组合。 所有参数都是可选的 看看这个示例: 我想要能够过滤基于每个参数或组合的2或参数。 我应该如何编写@RequestParam？这是一个复杂的查询。对此有何策略？

  • Java Specification CriteriaBuilder 复杂查询

    我对规范、构建器、查询不是很有经验，我必须做一个相当复杂的查询，就像这样: 我有一个这样的DTO: 对我来说很难。我什么都试过了。我宁愿给你看一个具体的案例，以免产生误解。有人能帮我吗？我将非常感激！ 我不需要使用规范，我只需要能够重现那个查询示例，规范似乎是最好的选择。 谢谢大家。

  • 复杂mongo数组查询

    给定集合"foo"，我们有字段"bar"，看起来像这样： 如何查询字段“bar”上满足以下条件的所有“foo”：[14,18]中的“uid”=2和“mid”

  • 复杂sql查询和JPQL

    如何将这个复杂的sql语句更改为JPQL？ 这是否可以在JPQL表单中显示？

相关问答

复杂sql查询和JPQLSpring boot MongoDb复杂查询SOAP信封响应xml验证@许多JPA 2复杂查询复杂的xml验证

相关文章

老生常谈 MyBatis 复杂查询制定复杂的Doctrine2 DQL查询将Soap XML响应转换为对象MySQL存储过程与复杂查询cakephp复杂查询多个“ OR”条件

相关阅读

Java SAX解析器 查询XML文档Java DOM解析器 查询XML文档Access重复查询向导GWT 复杂组件XML 查看

相关工具

Node-SOAPSOAP::LiteSOAP UDDIPHP SoapPHP-ip查询

相关文档

复杂性思维XML 中文教程响应式 Web 开发精讲Java XML 中文文档iOS 的函数响应型编程
快捷导航： 新手教程 算法原理 架构设计 Java进阶 数据库进阶 大厂专栏 面试经验 编程笔记 编程问答 所有专题 文档资料 工具软件 电子书籍 小牛导航
在线工具： 房贷计算器 个税计算器 Linux命令查询 Json格式化 正则表达式 颜色转换 AES加解密 SHA1加密 MD5加密 毒鸡汤 字数统计 随机密码生成 进制转换 Base64编解码 励志句子
Copyright © 2019-2024 小牛知识库@xnip.cn. All Rights Reserved.