Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

I have a the below sample XML schema that I am querying from in SQL Server 2012. I have a table with a XML column. I want to include the snippet of xml when each row is returned.

Below is the existing query and more detailed explanation.

<dev:Doc xmlns:dev="http://www.w3.org/2001/XMLSchema" Number="0" SchemaVersion="0.1" Settings="Testing" Title="Ordering">
  <dev:Forms FormId="A1">
    <dev:A1 ItemNumber="1" ItemGuid="{F402C584-555E-4D07-8C35-E88889B9DA44}">
      <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:Forms>
  <dev:Forms FormId="A2">
    <dev:A2 ItemNumber="1" ItemGuid="{3563F33E-B03A-4859-850E-A87D35BD8562}">
      <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 ItemNumber="2" ItemGuid="{CCFB2D5D-A23E-412D-8541-536451873713}">
      <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:Forms>
</dev:Doc>

Sample query

;WITH XMLNAMESPACES (Default 'http://www.w3.org/2001/XMLSchema' )
  SELECT 
     a.value('@Number[1]', 'int') as Number
    ,b.value('(@FormId)[1]', 'NVARCHAR(50)') Form
  --,XmlDocument.query('Doc/Forms') as FormXml, 
    ,c.value('@ItemGuid[1]', 'uniqueidentifier') as ItemGuid
    ,c.value('@ItemNumber[1]', 'INT') AS ItemNumber
    ,d.value('(@Name)[1]','nvarchar(50)') As  Visitor
    ,d.value('(@Location)[1]','nvarchar(50)') As Location
    ,e.value('(@Name)[1]', 'NVARCHAR(50)') As Sender        
  FROM
    XmlTable As X
  CROSS APPLY XmlDocument.nodes('Doc') As aa(a)
  CROSS APPLY a.nodes('Forms') As bb(b)
  CROSS APPLY b.nodes('*') As cc(c)
  CROSS APPLY c.nodes('Visitors/Visitor') as dd(d)
  CROSS APPLY c.nodes('Senders/Sender') as ee(e)

What I would like to do is include the snippet of xml after the Form column to show the snippet it came from.

I am trying this

    XmlDocument.query('Doc/Forms') as FormXml,    

The above is returning from that element down, I just want to return everything from <dev:Forms FormId="A1"> to the </dev:A1> for each row related to A1. So when Form is A2 I want A2 - /A2 <--- everything between

share|improve this question

1 Answer

up vote 1 down vote accepted

I believe what you want to get is the XML that is referenced by the b "table", no?

Try this line:

, b.query('.') as FormXml

Does that give you want you're looking for? It should give you the XML of the <Forms> node.

share|improve this answer
1  
Thanks marc, that is what I was looking for! – KeyboardFriendly Apr 24 at 20:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.