Solved

SQL XML

Posted on 2014-02-13
13
558 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 50 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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 450 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

717 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