SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Value and Nodes Method in select statement		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bbt2d
Starting Member

11 Posts
Posted - 01/30/2013 :  14:11:46  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1501 Posts
Posted - 01/30/2013 :  18:31:32  Show Profile  Reply with Quote
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

11 Posts
Posted - 01/30/2013 :  22:07:41  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1501 Posts
Posted - 01/31/2013 :  07:45:08  Show Profile  Reply with Quote
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

11 Posts
Posted - 02/01/2013 :  12:09:17  Show Profile  Reply with Quote
@ 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
Flowing Fount of Yak Knowledge

1501 Posts
Posted - 02/02/2013 :  15:43:20  Show Profile  Reply with Quote
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

876 Posts
Posted - 02/03/2013 :  04:28:24  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000