Solved

SQL XML

Posted on 2014-02-13
13
536 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
  • 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
 
LVL 16

Expert Comment

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

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 59

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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 59

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 59

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 59

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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.
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
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now