I am trying to learn XQuery and Xpath in SQL ServerI 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
ocument 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> <tstesciption>Normal Flow</tstesciption> </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> <tstesciption>Process Flow</tstesciption> </tst:SubTest> </tst:Events> <tst:IsReady>true</tst:IsReady> <tst:IsNotReady>false</tst:IsNotReady> </tst:A12> </tst:Formats></tstocument>\[/code\]Query I ranI just got a return, but it is still showing it in xml form:\[code\]Select XMLDoc.query('/*/*/*/*[local-name()=("Requestor", "Supplier")]') From XMLLoad\[/code\]
ocument 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> <tstesciption>Normal Flow</tstesciption> </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> <tstesciption>Process Flow</tstesciption> </tst:SubTest> </tst:Events> <tst:IsReady>true</tst:IsReady> <tst:IsNotReady>false</tst:IsNotReady> </tst:A12> </tst:Formats></tstocument>\[/code\]Query I ranI just got a return, but it is still showing it in xml form:\[code\]Select XMLDoc.query('/*/*/*/*[local-name()=("Requestor", "Supplier")]') From XMLLoad\[/code\]