I am just new to reading in an XML file into a table through SQL Server Management Studio. There are probably better ways but I would like to use this approach.
Currently I am reading in a standard XML file of records on people. A <record>
tag is the highest level of each row of data. I want to read all the records into separate rows into my SQL table.
I have gotten along fine so far using the following approach as follows:
SELECT
-- Record
category, editor, entered, subcategory, uid, updated,
-- Person
first_name, last_name, ssn, ei, title, POSITION,
FROM OPENXML(@hDoc, 'records/record/person/names')
WITH
(
-- Record
category [varchar](100) '../../@category',
editor [varchar](100) '../../@editor',
entered Datetime '../../@entered',
subcategory [varchar](100) '../../@subcategory',
uid BIGINT '../../@uid',
updated [varchar](100) '../../@updated',
-- Person
first_name [varchar](100) 'first_name',
last_name [varchar](100) 'last_name',
ssn [varchar](100) '../@ssn',
ei [varchar](100) '../@e-i',
title [varchar](100) '../title',
Position [varchar](100) '../position',
)
However this approach has worked fine as the tag names have all been unique to each record/person. The issue I have is within the <Person>
tag I now have an <Aliases>
tag that contains a list of more than 1 <Alias> test name </Alias>
tags. If I use the above approach & reference '../aliases' I get all the Alias elements as one long String row mixed together. If I just try '../aliases/alias' ONLY the first element is returned per record row. If there was 10 Alias elements within the Aliases tag set I would like 10 rows returned for example.
Is there a way to specify that when there are multiple tags of the same name within a higher level tag, return them all & not just one row?
The following is the example block within the XML I am referring to:
- <aliases>
<alias>test 1</alias>
<alias>test 2</alias>
<alias>test 3</alias>
</aliases>
I would like the following in the SQL table:
Record Aliases
Record 1 test 1
Record 1 test 2
Record 1 test 3
Record 2 test 4
Record 2 test 5
but all I get is:
Record 1 test 1
Record 2 test 4
Apologies if I have not explained this correctly - any help would be greatly appreciated.
As Antonio indicated, I would use XQuery instead of Openxml. I've guessed at your source xml, and provided code for your 2 queries:
/* Please note I have used different cases than you to follow a standard (first letter capitialised for elements, lowercase for attributes.
As xml is case sensitive, you may need to change the below code to suit the case of your data */
/* Bring your xml into an xml variable */
DECLARE @xml xml = '
<Records>
<Record category="category1" editor="editor1" entered="2015-01-01" subcategory="subcategory1" uid="100001" updated="updated1">
<Person ssn="ssn1" e-i="ei1">
<Title>Title1</Title>
<Position>Position1</Position>
<Names>
<First_name>FirstName1</First_name>
<Last_name>LastName1</Last_name>
<Aliases>
<Alias>test1</Alias>
<Alias>test2</Alias>
<Alias>test3</Alias>
</Aliases>
</Names>
</Person>
</Record>
<Record category="category2" editor="editor2" entered="2015-01-02" subcategory="subcategory2" uid="100002" updated="updated2">
<Person ssn="ssn2" e-i="ei2">
<Title>Title2</Title>
<Position>Position2</Position>
<Names>
<First_name>FirstName2</First_name>
<Last_name>LastName2</Last_name>
<Aliases>
<Alias>test4</Alias>
<Alias>test5</Alias>
</Aliases>
</Names>
</Person>
</Record>
</Records>'
/* The unary relationship of 1 element/attribute type per record in xpath */
SELECT T.rows.value('@category[1]', '[varchar](100)') AS Category
,T.rows.value('@editor[1]', '[varchar](100)') AS Editor
,T.rows.value('@entered[1]', '[Datetime]') AS Entered
,T.rows.value('@subcategory[1]', '[varchar](100)') AS Subcategory
,T.rows.value('@uid[1]', '[bigint]') AS [UID]
,T.rows.value('@updated[1]', '[varchar](100)') AS Updated
,T.rows.value('(Person/Names/First_name)[1]', '[varchar](100)') AS First_Name
,T.rows.value('(Person/Names/Last_name)[1]', '[varchar](100)') AS Last_Name
,T.rows.value('(Person/@ssn)[1]', '[varchar](100)') AS SSN
,T.rows.value('(Person/@e-i)[1]', '[varchar](100)') AS ei
,T.rows.value('(Person/Title)[1]', '[varchar](100)') AS Title
,T.rows.value('(Person/Position)[1]', '[varchar](100)') AS Position
FROM @xml.nodes('/Records/Record') T(rows)
/* Record to alias, one-to-many mapping */
SELECT T.rows.value('../../../../@category[1]', '[varchar](100)') AS Category
,T.rows.value('.', '[varchar](100)') AS Alias
FROM @xml.nodes('/Records/Record/Person/Names/Aliases/Alias') T(rows)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With