SQL XML

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>
dastaubAsked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
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
 
dastaubAuthor Commented:
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
 
Surendra NathConnect With a Mentor Technology LeadCommented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
dastaubAuthor Commented:
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
 
Surendra NathTechnology LeadCommented:
can you give us the SQL Statement that you used?
0
 
Kevin CrossChief Technology OfficerCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
dastaubAuthor Commented:
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
 
dastaubAuthor Commented:
Thank You.  Many times sophisticated code gets in the way of explaining the concept.
0
 
dastaubAuthor Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
"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
 
dastaubAuthor Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.