Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL XML

Posted on 2014-02-13
13
Medium Priority
?
564 Views
Last Modified: 2014-02-13
The below SQL query successfully returns a single column and row with XML.  How can I assign a column name to the column that is returned?

select FacNo,VisitID, HospAcct, MRN from Visit for XML path ('Visits'), ROOT ('Patients')

Inside the single column and row is the below XML.   I need to assign a column name to the column in order to make reference to it in code.

<Patients>
  <Visits>
    <FacNo>3</FacNo>
    <VisitID>156</VisitID>
    <HospAcct>007525009</HospAcct>
    <MRN>163988</MRN>
  </Visits>
  <Visits>
    <FacNo>3</FacNo>
    <VisitID>158</VisitID>
    <HospAcct>007525041</HospAcct>
    <MRN>036475</MRN>
  </Visits>
  <Visits>
    <FacNo>3</FacNo>
    <VisitID>160</VisitID>
    <HospAcct>007525108</HospAcct>
    <MRN>166346</MRN>
  </Visits>
</Patients>
0
Comment
Question by:dastaub
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
13 Comments
 

Author Comment

by:dastaub
ID: 39855592
the query

select * from (select FacNo,VisitID, HospAcct, MRN from Visit for XML path ('Visits'), ROOT ('Patients') ) y(ColumnName)

does give a column name  of "ColumnName" except SQL no longer sees the data as XML but as Text.
0
 
LVL 16

Assisted Solution

by:Surendra Nath
Surendra Nath earned 200 total points
ID: 39855660
try this out

DECLARE @Visit TABLE 
(
FacNO   INT, VisitID INT, HospAcct INT, MRN INT
)

INSERT INTO @Visit VALUES ( 3,156,007525009,163988)
INSERT INTO @Visit VALUES ( 3,158,007525041,036475)
INSERT INTO @Visit VALUES ( 3,160,007525108,166346)

select CAST(ABC AS XML) AS ABC FROM (
SELECT * FROM 
(
    select FacNo,VisitID, HospAcct, MRN from @Visit for XML path ('Visits'), ROOT ('Patients') 
) a(abc)) B

Open in new window

0
 

Author Comment

by:dastaub
ID: 39856248
your example does work, but if I replace the temp table with the actual table, it returns an empty column.  It does not create an error only an empty column.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39856282
can you give us the SQL Statement that you used?
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39856285
I think you just need the TYPE keyword.
SELECT FacNo,VisitID, HospAcct, MRN 
FROM Visit 
FOR XML PATH('Visits'), ROOT('Patients'), TYPE
;

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39856295
P.S. when you say you need to refer to it in code, are you using .NET?
If yes, you can read the XML directly into an XMLDocument, so you do not need to name the column.  Further, you can use the column index as you know there is only one XML string in result.
0
 

Author Comment

by:dastaub
ID: 39856720
Yes, VB.NET  
Can you post an example of what you just described in your P.S. above or send me to a link?
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 1800 total points
ID: 39856847
Hi.

Here is a simplified example, so please forgive me for lack of code sophistication.

Imports System.Data.SqlClient
Imports System.Xml

Module Module1

    Sub Main()
        Dim xmldoc As New XmlDocument()

        Using conn As New SqlConnection("Data Source=.;Initial Catalog=AdventureWorks2012;Integrated Security=True")
            conn.Open()
            'SELECT WorkOrderID AS [@ID]
            '     , ProductID AS [Product/@ID]
            '     , OrderQty AS [Product/@OrderQty]
            '     , DueDate AS [Product/@DueDate]
            'FROM AdventureWorks2012.Production.WorkOrder
            'WHERE WorkOrderID BETWEEN 10 AND 20
            'FOR XML PATH('WorkOrder'),ROOT('Production'),TYPE
            ';
            Using cmd As New SqlCommand("SELECT WorkOrderID AS [@ID], ProductID AS [Product/@ID], OrderQty AS [Product/@OrderQty], DueDate AS [Product/@DueDate] FROM AdventureWorks2012.Production.WorkOrder WHERE WorkOrderID BETWEEN 10 AND 20 FOR XML PATH('WorkOrder'),ROOT('Production'),TYPE;", conn)
                xmldoc.Load(cmd.ExecuteXmlReader())
            End Using
        End Using

        Console.Write(xmldoc.InnerXml())
        Console.ReadLine()
    End Sub

End Module

Open in new window

0
 

Author Comment

by:dastaub
ID: 39857102
Thank You.  Many times sophisticated code gets in the way of explaining the concept.
0
 

Author Comment

by:dastaub
ID: 39857121
When I Execute:
SELECT FacNo,VisitID, HospAcct, MRN FROM Visit FOR XML PATH('Visits'), ROOT('Patients'), TYPE       it return an empty column

When I Execute:
SELECT FacNo,VisitID, HospAcct, MRN FROM Visit FOR XML PATH('Visits'), ROOT('Patients')
       it returns the XML with a system generated column name.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39857159
"Empty column" - are you talking about the column name?  If so, I think it does remove that because it knows it is XML type in SSMS.  Does the VB.NET code display the XML at the console?  It did for me, so just curious.
0
 

Author Comment

by:dastaub
ID: 39857501
Empty column = blank where data would be, not the column name.  All the results are in SQL not in VB.NET.

the Column name is XML_F52E2B61-18A1-11dAb105-008005F49916B without TYPE
and (no column name) with TYPE


with XML_F52E2B61-18A1-11dAb105-008005F49916B the field contains XML data.
with  (no column name) the field is blank
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39858108
Hmm.  That is strange.  Is there something under the message tab?
Anyway, if the goal is to get this in VB.NET, you do not need to add TYPE.  The TYPE suggestion was for when you were using this as a sub-select and wanted to retain the XML data type.  For VB.NET, you can use the XmlReader approach I showed to get the information out without doing a derived query.
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question