Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Value and Nodes Method in select statement

Author  Topic 

bbt2d
Starting Member

13 Posts

Posted - 2013-01-30 : 14:11:46
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 table

INSERT 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 Sender
FROM
XmlTable

CROSS 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 correct


Title Functions Number VisitorName Location Sender
======== ========= ====== =========== ======== ======
Ordering A1 1 Dev01 STLRF NULL
Ordering A1 1 Dev02 STLRF NULL
Ordering A1 1 Dev03 FGRTY NULL
Ordering A2 NULL NULL NULL NULL

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-30 : 18:31:32
I may be missing something, but the query that you posted and the columns in the required output don't match. For example, the first column being queried is the Title attribute, but the results you want has RevNumber as the first column.

Try this to see if it is close to what you are looking for:
;WITH XMLNAMESPACES ( 'http://www.w3.org/2001/XMLSchema' as dev )
SELECT
Document.value('(dev:Base/@RevisionNumber)[1]', 'NVARCHAR(MAX)') RevNumber,
Functions.value('@Id','nvarchar(32)') AS FunctionId,
A.value('@Number','INT') AS Number,
Visitor.value('@Name','nvarchar(32)') AS Visitor,
Visitor.value('@Location','nvarchar(32)') AS Location,
Sender.value('@Name','nvarchar(32)') AS Sender
FROM
XmlTable
CROSS APPLY
xmlDocument.nodes('dev:Doc') As XD(Document)
CROSS APPLY
Document.nodes('dev:Functions') As XD2(Functions)
Outer APPLY
Functions.nodes('dev:*') As XD3(A)
OUTER APPLY
A.nodes('dev:Visitors/dev:Visitor') As XD4(Visitor)
OUTER APPLY
A.nodes('dev:Senders/dev:Sender') As XD5(Sender)
Go to Top of Page

bbt2d
Starting Member

13 Posts

Posted - 2013-01-30 : 22:07:41
Yea, That is what I wanted, and yea, I changed the rev to title just messing around with the query, I know that wouldnt change anything. So can you tell me what the * does for the function nodes, and I see you applied the last 2 to the same nodes, could you explain why and tell me anywhere else I messed up? @ James thanks again!
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-31 : 07:45:08
The * in dev:* is a wildcard, indicating that I want to pick every direct descendant of the Functions node.

The reason for querying against the same node in the last two outer apply clauses is that both Visitor and Sender are siblings - i.e., children of the same node. Using two outer applys to the descendants of the same node can cause another problem (or behavior depending on how you look at it), which is that if there are N Visitor nodes and M Sender nodes that are its siblings, you will get MxN rows in the result.
Go to Top of Page

bbt2d
Starting Member

13 Posts

Posted - 2013-02-01 : 12:09:17
@ James, thanks again for the comments! Looking @ the query itself can it be written better to avoid the possible behavioral problems mentioned in your earlier comments!?

Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-02 : 15:43:20
If you indeed have for example, 3 Visitor nodes and 2 Sender nodes, all being under the same parent node, what do you want to get? There is really no good way to associate or distribute the 3 Visitors to the 2 senders, is there? Now, if you were guaranteed that there will always be only one Sender (even if you have multipler Visitors) or vice versa, this would not be an issue. If that is the case, there is nothing more you need to do.
Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2013-02-03 : 04:28:24
If you're going to do anything meaningful with this data, I'd suggest you extract it into a proper relational model rather than try fiddling about with XML whenever you need anything. If it were me, I'd probably do it at the time you load the XML and not bother keeping it in that form anyway. It depends on what you're doing and why of course.
Go to Top of Page
   

- Advertisement -