I have a table!CREATE TABLE [dbo].[XmlTable]( [XmlId] [int] IDENTITY(1,1) NOT NULL, [XmlDocument] [xml] NOT NULL, CONSTRAINT [PK_XmlTable] PRIMARY KEY CLUSTERED ( [XmlId] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO
With a schema structure: stored in the xml column<dev:Doc xmlns:dev="http://www.w3.org/2001/XMLSchema" SchemaVersion="0.1" Settings="Testing" Title="Ordering"> <dev:Base RevisionNumber="0" Baseid="34433" /> <dev:Rev Time="2013-01-21T15:08:00"> <dev:Person Name="Me" Systemid="54654" /> </dev:Rev> <dev:Functions Id="A1"> <dev:A1 Number="1"> <dev:Codes>D</dev:Codes> <dev:Required>true</dev:Required> <dev:Informational>false</dev:Informational> <dev:Visitors> <dev:Visitor Name="Dev01" Location="STLRF"> <dev:Divisions> <dev:Division Number="1" Name="TFR3" Usage="Monitor"> <dev:Description>Development Fundamentals</dev:Description> </dev:Division> <dev:Division Number="2" Name="DEF32" Usage="Monitor"> <dev:Description>Testing Fundamentals</dev:Description> </dev:Division> <dev:Division Number="3" Name="DEP13" Usage="None"> <dev:Description>Guided Fundamentals</dev:Description> </dev:Division> </dev:Divisions> </dev:Visitor> <dev:Visitor Name="Dev02" Location="STLRF"> <dev:Divisions> <dev:Division Number="1" Name="TFR3" Usage="Monitor"> <dev:Description>Development Fundamentals</dev:Description> </dev:Division> <dev:Division Number="2" Name="DEF32" Usage="Monitor"> <dev:Description>Testing Fundamentals</dev:Description> </dev:Division> <dev:Division Number="3" Name="DEP13" Usage="None"> <dev:Description>Guided Fundamentals</dev:Description> </dev:Division> </dev:Divisions> </dev:Visitor> <dev:Visitor Name="Dev03" Location="FGRTY"> <dev:Divisions> <dev:Division Number="1" Name="TFR3" Usage="Monitor"> <dev:Description>Development Fundamentals</dev:Description> </dev:Division> <dev:Division Number="2" Name="DEF32" Usage="Monitor"> <dev:Description>Testing Fundamentals</dev:Description> </dev:Division> <dev:Division Number="3" Name="DEP13" Usage="None"> <dev:Description>Guided Fundamentals</dev:Description> </dev:Division> </dev:Divisions> </dev:Visitor> </dev:Visitors> <dev:Senders> <dev:Sender Name="FGY(14A)" /> </dev:Senders> </dev:A1> </dev:Functions> <dev:Functions Id="A2"> <dev:A2 Number="1"> <dev:Codes>C</dev:Codes> <dev:Required>true</dev:Required> <dev:Informational>false</dev:Informational> <dev:Remarks>Support</dev:Remarks> <dev:Notes>Ready</dev:Notes> <dev:Visitors> <dev:Visitor Name="GHFF"> <dev:Divisions> <dev:Division Number="0" Name="Trial" Usage="None"> <dev:FromLocation>LOPO</dev:FromLocation> <dev:ToLocation>RDSS</dev:ToLocation> <dev:Description>Rich Filter</dev:Description> </dev:Division> </dev:Divisions> </dev:Visitor> </dev:Visitors> <dev:Senders> <dev:Sender Name="W33R" /> </dev:Senders> <dev:IsReady>true</dev:IsReady> <dev:IsCall>false</dev:IsCall> </dev:A2> <dev:A2 Number="2"> <dev:Codes>A</dev:Codes> <dev:Required>true</dev:Required> <dev:Informational>false</dev:Informational> <dev:Remarks>Loader Ready</dev:Remarks> <dev:Notes>Ready</dev:Notes> <dev:Visitors> <dev:Visitor Name="UDT"> <dev:Divisions> <dev:Division Number="0" Name="Trial" Usage="None"> <dev:FromLocation>TYUJ</dev:FromLocation> <dev:ToLocation>DETF</dev:ToLocation> <dev:Description>Web Enhance</dev:Description> </dev:Division> </dev:Divisions> </dev:Visitor> </dev:Visitors> <dev:Senders> <dev:Sender Name="RJ4" /> </dev:Senders> <dev:IsReady>true</dev:IsReady> <dev:IsCall>false</dev:IsCall> </dev:A2> </dev:Functions></dev:Doc>
I am trying to return the Revision Number, Functions id, number, Visitor, location of the visitors, Sender name,Something like:RevNumber Function Id Number Visitor Location Sender========= =========== ======== ======= ======== ====== 0 A1 1 Dev01 STLRF FGY(14A) 0 A1 1 Dev02 STLRF FGY(14A) 0 A1 1 Dev03 FGRTY FGY(14A) 0 A2 1 GHFF NULL W33R 0 A2 2 UDT NULL RJ4
Here is my insert into the tableINSERT INTO XmlTable(XMLDocument)SELECT * FROM OPENROWSET( BULK 'C:\Users\123\Desktop\Practice.xml', SINGLE_BLOB) AS x;
I have used the query, and values method to pull back a partial list but now i am stuck. I appreciate any help. Thanks!Here is the query;WITH XMLNAMESPACES ( 'http://www.w3.org/2001/XMLSchema' as dev )SELECT Document.value('@Title' , 'NVARCHAR(MAX)') Title, Functions.value('@Id', 'NVARCHAR(MAX)') Functions, A1.value('@Number', 'INT') Number, Visitor.value('@Name', 'NVARCHAR(MAX)') AS VisitorName, Visitor.value('@Location', 'NVARCHAR(MAX)') AS Location, Sender.value('@Name', 'NVARCHAR(MAX)') As SenderFROM XmlTableCROSS APPLY xmlDocument.nodes('dev:Doc') As XD(Document)CROSS APPLY Document.nodes('dev:Functions') As XD2(Functions)Outer APPLY Functions.nodes('dev:A1') As XD3(A1)OUTER APPLY A1.nodes('dev:Visitors/dev:Visitor') As XD4(Visitor)OUTER APPLY Visitor.nodes('dev:Senders/dev:Sender') As XD5(Sender)
Results I am getting that are not all correctTitle Functions Number VisitorName Location Sender======== ========= ====== =========== ======== ======Ordering A1 1 Dev01 STLRF NULLOrdering A1 1 Dev02 STLRF NULLOrdering A1 1 Dev03 FGRTY NULLOrdering A2 NULL NULL NULL NULL