Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2013
    Posts
    5

    Unanswered: Query data from xml column with xquery

    I am trying to learn XQuery and Xpath in SQL Server

    I created a sample file and uploaded it to a Table with 2 columns ID, XMLDoc. The below code is within the document in the XMLDoc column so it is the only record in the column.

    I am trying to query the file so it will show all the results in a table like a normal select statement would. How would you construct the select statement to select all the information like a select * ? How would you select one field like all suppliers? I would like to select the supplier, requestor for each item.

    Here is the xml:



    Code:
    <tst:Document xmlns:tst ="http://www.w3.org/2001/XMLSchema" SchemaVersion="0.1" Classification="Test" UniqueIdentifier="1234" Title="Test">
      <tst:Revision RevNumber="0" TimeStamp="2013-01-21T12:56:00">
        <tst:Author Name="Me" Guid="1234" />
      </tst:Revision>
      <tst:Formats>
        <tst:A12 Item="1">
          <tst:Requestor Name="ADC" />
          <tst:Supplier Name="BBC" />
          <tst:Code>B</tst:Code>
          <tst:IsRequirement>true</tst:IsRequirement>
          <tst:IsNotRequired>false</tst:IsInformation>
          <tst:Remarks>ADC (Random Input Section)</tst:Remarks>
          <tst:Notes>Next Round.</tst:Notes>
          <tst:Events>
            <tst:SubTest Item="0">
              <tst:BLDG>BLDG1</tst:BLDG>
              <tst:BLDG2>BLDG2</tst:BLDG2>
              <tst:Function>Testing</tst:Function>
              <tst:Desciption>Normal Flow</tst:Desciption>
            </tst:SubTest>
          </tst:Events>
          <tst:IsReady>true</tst:IsReady>
          <tst:IsNotReady>false</tst:IsNotReady>
        </tst:A12>
        <tst:A12 Item="2">
          <tst:Requestor Name="ADC" />
          <tst:Supplier Name="BBC" />
          <tst:Code>A</tst:Code>
          <tst:IsRequirement>true</tst:IsRequirement>
          <tst:IsInformation>false</tst:IsInformation>
          <tst:Remarks>Requirement Not yet met.</tst:Remarks>
          <tst:Notes>Ready.</tst:Notes>
          <tst:Events>
            <tst:SubTest Item="0">
              <tst:BLDG>BLDG3</tst:BLDG>
              <tst:BLDG2>BLDG4</tst:BLDG2>
              <tst:TotalEvents>1</tst:TotalEvents>
              <tst:Function>Development</tst:Function>
              <tst:Desciption>Process Flow</tst:Desciption>
            </tst:SubTest>
          </tst:Events>
          <tst:IsReady>true</tst:IsReady>
          <tst:IsNotReady>false</tst:IsNotReady>
        </tst:A12>
      </tst:Formats>
    </tst:Document>

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,819
    Provided Answers: 14
    This is a rather broad topic. Start here, and see if that helps.

    Stairway to XML: Level 4 - Querying XML Data - SQLServerCentral

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •