?
Solved

SQL XML

Posted on 2014-02-13
13
Medium Priority
?
563 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
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 
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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

752 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