t-sql for XML

Question

Post reply

t-sql for XML

Positive

SSC-Addicted

Points: 483
More actions
December 28, 2009 at 10:51 am

#228762

XML
<?xml version="1.0" encoding="utf-8" standalone="no"?>
<submission>
<header_record>
<submission_date>2002-10-15</submission_date>
<begin_posting_date>2002-04-01</begin_posting_date>
<end_posting_date>2002-04-30</end_posting_date>
<number_of_records_transmitted>8</number_of_records_transmitted>
</header_record>
<detail_record>
<status>Good</status>
<name>Z<name>
</detail_record>
</submission>
SQL for above XML:
CREATE TABLE XmlImportTest
(
xmlFileName VARCHAR(300),
xml_data xml
)
GO
DECLARE @xmlFileName VARCHAR(300)
SELECT @xmlFileName = 'c:\myworkingexample.xml'
-- dynamic sql is just so we can use @xmlFileName variable in OPENROWSET
EXEC('
INSERT INTO XmlImportTest(xmlFileName, xml_data)
SELECT ''' + @xmlFileName + ''', xmlData
FROM
(
SELECT *
FROM OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA)
')
GO
SELECT * FROM XmlImportTest
--List XML in relational format
DECLARE @xmlDoc NVARCHAR(max)
DECLARE @handle INT
select @xmlDoc = cast((select xml_data from dbo.XmlImportTest) as nvarchar(max))
EXEC sp_xml_preparedocument @handle OUTPUT, @xmlDoc
SELECT
tab.col.value('./status[1]','varchar(1)') AS 'status',
tab.col.value('./name[1]','varchar(2)') AS 'name'
FROM [XmlImportTest]
CROSS APPLY
xml_data.nodes('submission/detail_record') AS tab(col)
GO
Curve Ball Challenge:
The above t-sql does not work if <submission> tag has more header data as listed below:
<submission fc:submission_type="CURVEBALL" xmlns="http://www.curveballxml.org" xmlns:fc="http://www.curveballxml.org" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.curveballxml.org https://challengeyouxml.org/schema/fc_submission.xsd">
Kindly suggest resources to help me overcome the challenge. Thank you.
+ive

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

You'll have to include the namespace in the query too...

declare @sample table (y XML)

insert @sample

values('

<submission fc:submission_type="CURVEBALL" xmlns="http://www.curveballxml.org"

xmlns:fc="http://www.curveballxml.org" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation="http://www.curveballxml.org https://challengeyouxml.org/schema/fc_submission.xsd">

<header_record>

<submission_date>2002-10-15</submission_date>

<begin_posting_date>2002-04-01</begin_posting_date>

<end_posting_date>2002-04-30</end_posting_date>

<number_of_records_transmitted>8</number_of_records_transmitted>

</header_record>

<detail_record>

<status>Good</status>

<name>Z</name>

</detail_record>

</submission>')

;WITH XMLNAMESPACES('http://www.curveballxml.org' AS e)

SELECTtab.col.value('(./e:status)[1]','varchar(10)') AS [status],

tab.col.value('(./e:name)[1]','varchar(2)') AS [name]

FROM@sample

CROSS APPLYy.nodes('e:submission/e:detail_record') AS tab(col)

N 56°04'39.16"
E 12°55'05.25"

Mauve SSChampion Points: 11316 More actions · Answer 2

See Jacob Sebastian's XML articles:

XML Workshop X - Working with namespaces

http://www.sqlservercentral.com/articles/XML/61333/

XML Workshop XI - Default Namespaces

http://www.sqlservercentral.com/articles/XML/61334/

[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]