Geek Answers Handbook

Sql query to output xml output

I have these tables

Table 1 tbl1_site [facilityId] [name]

table 2 tbl2_applicant [pvid] [facilityId] [npi] [firstname]

FK connection key: tbl1_site.facilityId = tbl2_applicant.facilityId

Table 3 tbl3_abstraction [pvid] [patientnumber] [diabetesdiagnosis] [dateofbirth]

FK connection key: tbl2_applicant.pvId = tbl3_abstraction.pvId

I had a problem creating an SQL query to reproduce this xml output.

thank

<account>
    <metadata />
    <practice-sites>
        <practice-site>
            <metadata>
                <data-element id="name">
                    <value>My Own Diabetes Medical Center</value>
                </data-element>
            </metadata>
            <applicants>
                <metadata />
                <applicant>
                    <metadata>
                        <data-element id="npi">
                            <value>1234567890</value>
                        </data-element>
                        <data-element id="firstname">
                            <value>Joseph</value>
                        </data-element>
                    </metadata>
                    <clinical-abstractions>                           
                        <clinical-abstraction>
                            <data-element id="diabetesdiagnosis">
                                <value>Backward</value>
                            </data-element>
                            <data-element id="dateofbirth">
                                <value>02/01/2009</value>
                            </data-element>
                            <data-element id="patientnumber">
                                <value>1</value>
                            </data-element>
                        </clinical-abstraction>
                    </clinical-abstractions>
                </applicant>
            </applicants>
        </practice-site>
    </practice-sites>
</account>
+5
source share
6 answers

Do you really need all those tags? I mean "metadata" and "data item"

try this query, it displays the data in the desired format:

select  t1.name as PracticeSite,
        (SELECT t2.npi as NPI, 
                t2.firstname,
                (SELECT t3.patientnumber, 
                        t3.diabetesdiagnosis, 
                        t3.dateofbirth
                        FROM tbl3_abstraction t3
                        WHERE t3.pvId=t2.pvId
                        FOR XML PATH('clinical-abstraction'), TYPE
                        ) as 'clinical-abstractions'
                FROM tbl2_applicant t2
                WHERE t1.[facilityId]=t2.[facilityId]
                FOR XML PATH('Applicant'), TYPE
        ) AS 'Applicants'
from tbl1_site t1
FOR XML path('PracticeSites'), root('account'), ELEMENTS;
+5
source

, - SQL-, "SELECT [...] FROM any AS XML", XML-, SQL Server. XML XSLT. SQL Server .NET.

+4

, . , - , , . , , . CONVERT, XML.

. SQL Server Management Studio Text Grid ( - Ctrl-T),

SET NOCOUNT ON
-- Create some tables to work with (you should have these already)
CREATE TABLE tbl1_site ([facilityId] INT, [name] VARCHAR(100))
CREATE TABLE tbl2_applicant([pvid] INT, [facilityId] INT, [npi] BIGINT, [firstname] VARCHAR(100))
CREATE TABLE tbl3_abstraction([pvid] INT, [patientnumber] INT, [diabetesdiagnosis] VARCHAR(100), [dateofbirth] DATETIME)

-- Add some basic data
INSERT INTO tbl1_site SELECT 100, 'My Own Diabetes Medical Center'
INSERT INTO tbl2_applicant SELECT 10, 100, 1234567890, 'Joseph'
INSERT INTO tbl3_abstraction SELECT 10, 1, 'Backward', '02/01/2009' 

-- For testing (here some more data - uncomment if desired)
--INSERT INTO tbl3_abstraction SELECT 10, 2, 'Forward', '03/04/2009' 
--INSERT INTO tbl1_site SELECT 200, 'My Other Diabetes Medical Center'
--INSERT INTO tbl2_applicant SELECT 10, 200, 9876543210, 'Conrad'
--INSERT INTO tbl3_abstraction SELECT 10, 3, 'Up', '02/01/2009' 
--INSERT INTO tbl3_abstraction SELECT 10, 4, 'Down', '03/04/2009' 

-- Declare some table variables and populate them with the required information from the base tables
-- We will use these to loop through
DECLARE @tbl1_site TABLE (tbl1Id INT IDENTITY(1, 1) PRIMARY KEY, [facilityId] INT, [name] VARCHAR(100))
INSERT INTO @tbl1_site(facilityId, name)
SELECT facilityId, name FROM tbl1_site

DECLARE @tbl2_applicant TABLE (tbl2Id INT IDENTITY(1, 1) PRIMARY KEY, [pvid] INT, [facilityId] INT, [npi] BIGINT, [firstname] VARCHAR(100))
INSERT INTO @tbl2_applicant([pvid], [facilityId], [npi], [firstname])
SELECT [pvid], [facilityId], [npi], [firstname] FROM tbl2_applicant

DECLARE @tbl3_abstraction TABLE (tbl3Id INT IDENTITY(1, 1) PRIMARY KEY, [pvid] INT, [patientnumber] INT, [diabetesdiagnosis] VARCHAR(100), [dateofbirth] DATETIME)
INSERT INTO @tbl3_abstraction([pvid], [patientnumber], [diabetesdiagnosis], [dateofbirth])
SELECT [pvid], [patientnumber], [diabetesdiagnosis], [dateofbirth] FROM tbl3_abstraction

-- Additional variables to track rows and field values
DECLARE @Maxtbl1Id INT
DECLARE @Curtbl1Id INT
DECLARE @facilityId INT

DECLARE @Maxtbl2Id INT
DECLARE @Curtbl2Id INT
DECLARE @pvid INT

DECLARE @Maxtbl3Id INT
DECLARE @Curtbl3Id INT

-- Declare an output table to hold everything in
DECLARE @OutputTable TABLE (OutputTableId INT IDENTITY(1, 1) PRIMARY KEY, XmlElement VARCHAR(8000))

-- Here is the top of the XML
INSERT INTO @OutputTable(XmlElement)
SELECT '<account>
<metadata />
<practice-sites>'

-- Loop through all the practice sites
SELECT @Maxtbl1Id = MAX(t.tbl1Id), @Curtbl1Id = MIN(t.tbl1Id) FROM @tbl1_site t

WHILE (@Maxtbl1Id >= @Curtbl1Id)
BEGIN
    INSERT INTO @OutputTable(XmlElement)
    SELECT '        <practice-site>
        <metadata>
            <data-element id="name">
                <value>' + t.name + '</value>
            </data-element>
        </metadata>'
      FROM @tbl1_site t
     WHERE tbl1Id = @Curtbl1Id

    SELECT @facilityId = t.facilityId
      FROM @tbl1_site t
     WHERE tbl1Id = @Curtbl1Id

    INSERT INTO @OutputTable (XmlElement)
    SELECT '            <applicants>
            <metadata />'

    -- Loop through all the applicants
    SELECT @Maxtbl2Id = MAX(t.tbl2Id), @Curtbl2Id = MIN(t.tbl2Id) FROM @tbl2_applicant t WHERE t.facilityId = @facilityId

    WHILE (@Maxtbl2Id >= @Curtbl2Id)
    BEGIN
        INSERT INTO @OutputTable(XmlElement)
        SELECT '                <applicant>
                <metadata>
                    <data-element id="npi">
                        <value>' + CONVERT(VARCHAR(100), t.npi) + '</value>
                    </data-element>
                    <data-element id="firstname">
                        <value>' + t.firstname + '</value>
                    </data-element>
                </metadata>'
          FROM @tbl2_applicant t
         WHERE tbl2Id = @Curtbl2Id

        SELECT @pvid = t.pvid
          FROM @tbl2_applicant t
         WHERE tbl2Id = @Curtbl2Id

        -- Loop through all the abstractions
        INSERT INTO @OutputTable(XmlElement)
        SELECT '                    <clinical-abstractions>                           
                    <clinical-abstraction>
                        <data-element id="diabetesdiagnosis">
                            <value>' + diabetesdiagnosis + '</value>
                        </data-element>
                        <data-element id="dateofbirth">
                            <value>' + CONVERT(VARCHAR(10), dateofbirth, 1) + '</value>
                        </data-element>    
                        <data-element id="patientnumber">
                            <value>' + CONVERT(VARCHAR(10), patientnumber) + '</value>
                        </data-element>
                    </clinical-abstraction>
                </clinical-abstractions>'
        FROM tbl3_abstraction t
        WHERE t.pvid = @pvid

        INSERT INTO @OutputTable(XmlElement)
        SELECT '                </applicant>
        </applicants>'

        -- Increment our counter
        SET @Curtbl2Id = @Curtbl2Id + 1
    END

    INSERT INTO @OutputTable(XmlElement)
    SELECT '        </practice-site>
</practice-sites>'

    -- Increment our counter
    SET @Curtbl1Id = @Curtbl1Id + 1
END

-- Close off XML
INSERT INTO @OutputTable(XmlElement)
SELECT '</account>'

-- Return our result set
SELECT t.XmlElement
  FROM @OutputTable t
 ORDER BY t.OutputTableId ASC
+2

, SQL, , SQL- XML . FOR XML select. doc Microsoft , .

+1

, SQL. . xml , . , XML , SQL Server, . @Bernard, xml , , ?

xml, , @Diego, , .

FOR XML PATH, xml, , , ( , ). , , - - FOR XML EXPLICIT. , , , , .

FOR XML EXPLICIT :
1. / ( SQL)
2. , .

select 1 as tag,
       null as parent,
       null as [account!1],
       null as [metadata!2],
       null as [practice-sites!3],
       null as [practice-site!4],
       null as [metadata!5],
       null as [data-element!6!id],
       null as [data-element!6!value!ELEMENT],
       null as [applicants!7],
       null as [metadata!8],
       null as [applicant!9],
       null as [metadata!10],
       null as [data-element!11!id],
       null as [data-element!11!value!ELEMENT],
       null as [data-element!12!id],
       null as [data-element!12!value!ELEMENT],
       null as [clinical-abstractions!13],
       null as [clinical-abstraction!14],
       null as [data-element!15!id],
       null as [data-element!15!value!ELEMENT],
       null as [data-element!16!id],
       null as [data-element!16!value!ELEMENT],
       null as [data-element!17!id],
       null as [data-element!17!value!ELEMENT]

union all
select 2,
       1,
       null, null, null, null, null, null, null, null, null, null, null, null,
       null, null, null, null, null, null, null, null, null, null, null

union all
select 3,
       1,
       null, null, null, null, null, null, null, null, null, null, null, null,
       null, null, null, null, null, null, null, null, null, null, null       

union all
select 4,
       3,
       null, null, null, null, null, null, null, null, null, null, null, null,
       null, null, null, null, null, null, null, null, null, null, null

union all
select 5,
       4,
       null, null, null, null, null, null, null, null, null, null, null, null,
       null, null, null, null, null, null, null, null, null, null, null

union all
select 6,
       5,
       null,
       null,
       null,
       null,
       null,
       'name',
       s.name,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null   
from dbo.tbl1_Site s
where s.facilityID = @facilityID

union all
select 7,
       4,
       null, null, null, null, null, null, null, null, null, null, null, null,
       null, null, null, null, null, null, null, null, null, null, null

union all
select 8,
       7,
       null, null, null, null, null, null, null, null, null, null, null, null,
       null, null, null, null, null, null, null, null, null, null, null

union all
select 9,
       7,
       null, null, null, null, null, null, null, null, null, null, null, null,
       null, null, null, null, null, null, null, null, null, null, null

union all
select 10,
       9,
       null, null, null, null, null, null, null, null, null, null, null, null,
       null, null, null, null, null, null, null, null, null, null, null

union all
select 11,
       10,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       'npi',
       ap.npi,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null
from dbo.tbl2_Applicant ap
where ap.pvid = @pvid

union all
select 12,
       10,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       'firstname',
       ap.firstname,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null
from dbo.tbl2_Applicant ap
where ap.pvid = @pvid

union all
select 13,
       9,
       null, null, null, null, null, null, null, null, null, null, null, null,
       null, null, null, null, null, null, null, null, null, null, null

union all
select 14,
       13,
       null, null, null, null, null, null, null, null, null, null, null, null,
       null, null, null, null, null, null, null, null, null, null, null

union all
select 15,
       14,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       'diabetesdiagnosis',
       a.diabetesdiagnosis,
       null,
       null,
       null,
       null
from dbo.tbl3_Abstraction a
where a.patientnumber = @patientnumber

union all
select 16,
       14,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       'dateofbirth',
       convert(varchar(10), a.dateofbirth, 101),
       null,
       null
from dbo.tbl3_Abstraction a
where a.patientnumber = @patientnumber

union all
select 17,
       14,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       'patientnumber',
       a.patientnumber
from dbo.tbl3_Abstraction a
where a.patientnumber = @patientnumber

for xml explicit

. / SQL, , , , , . , , , , . , .

0

XML ? BL? , , , SQL.

0

More articles:


All Articles