Python - Convert XML field from SQL SERVER into XML FILE using pyodbc

Question

I have the next code:

import pypyodbc as pyodbc

cnxn = pyodbc.connect(
    'Trusted_Connection=yes;DRIVER={SQL Server};SERVER=localhost;DATABASE=HOLA;UID=sa;PWD=HOLAMUNDO'
)

idPerson = 2

cursor = cnxn.cursor()
cursor.execute('SELECT * FROM prueba WHERE id=?', (idPerson,))
for row in cursor.fetchall():
    print(row)

This works fine, and the selected XML field have the next format:

<PERSON>
  <INFO>
    <NAME>Charlie</NAME>
    <LASTNAME>Brown</LASTNAME>
  </INFO>
</PERSON>

How can I take that field and save it in a new XML file in a directory...?

This is the script for my database:

USE [HOLA]
GO
/****** Object:  Table [dbo].[prueba]    Script Date: 04/03/2016 8:29:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[prueba](
    [id] [int] NOT NULL,
    [xml] [xml] NULL,
 CONSTRAINT [PK_prueba] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Simply save the row string to file.

Parfait
– Parfait

2016-03-04 18:39:22 +00:00
Commented Mar 4, 2016 at 18:39 — Parfait
– Parfait, Commented Mar 4, 2016 at 18:39

CyborgNinja23 · Accepted Answer · 2016-03-04 17:40:05Z

3

Solved, in the next way...

import pypyodbc as pyodbc
import urllib
from xml.dom.minidom import parse, parseString
xmlpath = "example.xml"

cnxn = pyodbc.connect(
    'Trusted_Connection=yes;DRIVER={SQL Server};SERVER=localhost;DATABASE=HOLA;UID=sa;PWD=HOLAMUNDO'
)

idPerson = 2

cursor = cnxn.cursor()
cursor.execute('SELECT id, xml FROM prueba WHERE id=?', (idPerson,))

for row in cursor.fetchall():
    print row[1]

xml = row[1]

#Generate the xml file from the string
dom = parseString(xml)

# Write the new xml file
xml_str = dom.toprettyxml(indent="  ")
with open("example.xml", "w") as f:
    f.write(xml_str)

answered Mar 4, 2016 at 17:40

CyborgNinja23

2991 gold badge12 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mbh86 Over a year ago

To make it work with Python 3, change with with open("example.xml", "wb") as f

mbh86 Over a year ago

Also if someone encounters error with encoding, I used: f.write(xml_str.encode('utf8'))

Collectives™ on Stack Overflow

Python - Convert XML field from SQL SERVER into XML FILE using pyodbc

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related