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

    Unanswered: Xquery -- Select not returning all the attributes/values

    I have a table:

    Code:
    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:



    Code:
     <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 id, number, name, and location of the visitors

    Something like:
    Code:
    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 the table insert

    Code:
    INSERT INTO XmlTable(XMLDocument)
    SELECT * FROM OPENROWSET(
       BULK 'C:\Users\123\Desktop\Practice.xml',
       SINGLE_BLOB) AS x;

    I Have gotten a little further, but the number is not showing for A2 and the Sender, visitor is showing null.

    Code:
    ;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)
    GO
    Results I am getting that are not all correct:

    Code:
      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
    Not I changed Rev to Title
    Last edited by bbt2d; 01-30-13 at 16:57.

  2. #2
    Join Date
    Jan 2013
    Posts
    5
    I got the query to work using a dev:* on the first outer apply instead of A1

    Then having the last 2 outer applies point to the first. Thanks Issue Resolved

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
  •