We all know that there are different ways you can write a query to retrieve a piece of information from one or more tables. XQuery is a rich query language and you can write an XQuery expression in a number of ways to retrieve a certain information. I will show you a few examples in this post.
Here is the sample XML document we will use for this lab.
<Employee>
<ContactInfo>
<Info Name="Email">jacob.reliancesp[at]gmail.com</Info>
<Info Name="Phone">+919979882144</Info>
<Info Name="IM">jacob[at]excellenceinfonet.com</Info>
</ContactInfo>
</Employee>
Let us try to read the "Email" information from the XML instance given above. Here are a few different XQuery expressions which reads the "email" information from the above XML.
DECLARE @x XML
SELECT @x = '
<Employee>
<ContactInfo>
<Info Name="Email">jacob.reliancesp[at]gmail.com</Info>
<Info Name="Phone">+919979882144</Info>
<Info Name="IM">jacob[at]excellenceinfonet.com</Info>
</ContactInfo>
</Employee>'
-- option 1
SELECT @x.value('data(/Employee/ContactInfo/Info[@Name="Email"])[1]', 'varchar(30)')
-- option 2
SELECT @x.value('(/Employee/ContactInfo/Info[@Name="Email"])[1]', 'varchar(30)')
-- option 3
SELECT
x.value('.','varchar(30)')
FROM @x.nodes('/Employee/ContactInfo/Info[@Name="Email"]') n(x)
-- option 4
SELECT
x.value('(Info[@Name="Email"])[1]','varchar(30)')
FROM @x.nodes('/Employee/ContactInfo') n(x)
-- option 5
SELECT
x.value('(ContactInfo/Info[@Name="Email"])[1]','varchar(30)')
FROM @x.nodes('/Employee') n(x)
-- option 6
SELECT
x.value('.','varchar(30)')
FROM @x.nodes('/Employee/ContactInfo/Info') n(x)
WHERE x.value('(.[@Name="Email"])[1]','varchar(30)') IS NOT NULL
-- option 7
SELECT
x.value('.','varchar(30)')
FROM @x.nodes('/Employee/ContactInfo/Info') n(x)
WHERE x.exist('(.[@Name="Email"])[1]') = 1
Though all the queries produce the same result, there may be performance differences if the XML document is larger. Also, there could be many more ways of writing this query too. These are just a few that came to the top of my mind.
In the previous post we have seen how to insert an attribute to an XML variable. In this post, let us see how to insert an element to an XML variable. In the example given below, an element named "LastName" is added to the "Employee" element.
DECLARE @x XML
SELECT @x = '
<Employee>
<FirstName>Jacob</FirstName>
</Employee>'
DECLARE @LastName VARCHAR(15)
SELECT @LastName = 'Sebastian'
-- insert an attribute
SET @x.modify('
insert element LastName {sql:variable("@LastName")} as last into
(/Employee)[1]
')
-- test the results
SELECT @x
/*
output:
<Employee>
<FirstName>Jacob</FirstName>
<LastName>Sebastian</LastName>
</Employee>
*/
We have seen how to update the value of an attribute and how to delete an attribute from an XML variable. Let us now see how to insert an attribute to an XML variable. We could do this by using the modify() method of XML data type. Note that XQuery methods are case sensitive.
The following example inserts an attribute named "LastName" to the "Employee" element.
DECLARE @x XML
SELECT @x = '<Employee FirstNam="Jacob"/>'
DECLARE @LastName VARCHAR(15)
SELECT @LastName = 'Sebastian'
-- insert an attribute
SET @x.modify('
insert attribute LastName {sql:variable("@LastName")} as last into
(/Employee)[1]
')
-- test the results
SELECT @x
/*
output:
<Employee FirstNam="Jacob" LastName="Sebastian" />
*/
In the previous lab we saw how to update the value of an attribute. Now let us see how to delete an attribute from an XML variable.
DECLARE @x XML
SELECT @x = '<Employee FirstName="Jacob" LastName="Sebastian"/>'
SET @x.modify(
'
delete (/Employee/@LastName)[1]
')
SELECT @x
/*
output:
<Employee FirstName="Jacob" />
*/
Note that XQuery is case sensitive and you should pay special attention to the 'casing'.
I just heard this question from a co-worker and wrote a small piece of sample code for him. Just wanted to share it with all of you. The following code snippet shows an example that updates the value of an attribute.
The XML fragment contains information of an employee and we are trying to update the "LastName" attribute. The current value is "Sebastian" and after update, it will become "Seb". This also shows how to access the value of a local variable from within the modify() method.
DECLARE @x XML
SELECT @x = '<Employee FirstName="Jacob" LastName="Sebastian"/>'
DECLARE @Lastname VARCHAR(10)
SELECT @LastName = 'Seb'
SET @x.modify(
'
replace value of (/Employee/@LastName)[1]
with sql:variable("@LastName")
')
SELECT @x
/*
output:
<Employee FirstName="Jacob" LastName="Seb" />
*/
This may be a very simple example. I don't know if this specific example would help anyone. However, the approach may give some of you a hint that can solve another problem that you may be trying to solve. In fact, the purpose of this whole series is to show some examples that might help some one solve a similar problem.
Here is the problem we are trying to attend in this session. A colleague needed a query that retrieves a comma separated list of values from an XML fragment. The XML is very simple. Here is how it looks like.
<Type Value="01" />
<Type Value="02" />
What we need to generate is a comma separated list as given below.
Lets start looking at the code.
DECLARE @x XML
SELECT @x = '
<Type Value="01"/>
<Type Value="02"/>'
SELECT @x.query('data(Type/@Value)') AS Val
/*
Val
-------------------------------------------
01 02
*/
The above query returns a space separated list with the values stored in the XML fragment. We used the "query()" method of XML data type. So far we have a space separated list of values. Now we can easily apply REPLACE() function on this value and turn it to a comma separated list.
Wait a second, there is a problem. The result returned by the "query()" method is an XML data type. Hence we cannot use it in the REPLACE function. We need to retrieve the string value from the XML result.
DECLARE @x XML
SELECT @x = '
<Type Value="01"/>
<Type Value="02"/>'
SELECT @x.query('data(Type/@Value)').value('.','varchar(100)') AS Val
/*
Val
-------------------------------------------
01 02
*/
The "value()" method of the XML data type retrieves a string value from the result produced by the "query()" method. Now let us apply the REPLACE() function on the results of the above query.
DECLARE @x XML
SELECT @x = '
<Type Value="01"/>
<Type Value="02"/>'
SELECT REPLACE(
@x.query('data(Type/@Value)').value('.','varchar(100)'),' ',','
) AS Val
/*
Val
-------------------------------------------
01,02
*/
Well, that gave us exactly what we were looking for. In the above example, we assigned the XML fragment to an XML variable and processed it. This is much easier to work with and understand. However, in the requirement I mentioned earlier in this post, a single query was needed. Here is a different version of the query that performs the entire operation in a single query.
SELECT REPLACE(
CAST(
'<Type Value="01"/><Type Value="02"/>' AS XML
).query('data(Type/@Value)').value('.','varchar(100)'),
' ',
','
)
I came across a post in the MSDN SQL Server XML forum asking for some sample code that reads information from the XML instance and inserts it into two tables. At first glance, the request looked simple. But then I realized, that while inserting the information to the tables, I need to establish a relationship between the rows.
Here is the XML instance that we need to process.
<Member>
<MemHeader>
<SrcCode>B</SrcCode>
<EID>100000</EID>
<MemID>HSG-200</MemID>
</MemHeader>
<AttributeList>
<Item>
<Groupname>NAME</Groupname>
<AttributeName>First</AttributeName>
<Currentvalue>Lee</Currentvalue>
</Item>
<Item>
<Groupname>NAME</Groupname>
<AttributeName>Middle</AttributeName>
<Currentvalue>Marvin</Currentvalue>
</Item>
<Item>
<Groupname>Name</Groupname>
<AttributeName>Last</AttributeName>
<Currentvalue>Bruised</Currentvalue>
</Item>
</AttributeList>
</Member>
<Member>
<MemHeader>
<SrcCode>C</SrcCode>
<EID>120202</EID>
<MemID>CTX-300</MemID>
</MemHeader>
<AttributeList>
<Item>
<Groupname>NAME</Groupname>
<AttributeName>First</AttributeName>
<Currentvalue>Barry</Currentvalue>
</Item>
<Item>
<Groupname>NAME</Groupname>
<AttributeName>Middle</AttributeName>
<Currentvalue>M</Currentvalue>
</Item>
<Item>
<Groupname>NAME</Groupname>
<AttributeName>Last</AttributeName>
<Currentvalue>Humphries</Currentvalue>
</Item>
<Item>
<Groupname>ADDRESS</Groupname>
<AttributeName>Line1</AttributeName>
<Currentvalue>40 Fore Street</Currentvalue>
</Item>
</AttributeList>
</Member>
The XML document contains HEADER and DETAIL information. HEADER part of the information should go to a parent table. Then the DETAIL part of the information should go to a child table. The PRIMARY key of the parent table should be updated in the CHILD table to establish a correct PKEY-FKEY relationship.
So we need to insert the header information to the header table first, and then insert the information to the detail table. When inserting information to the detail table, we need to find a way to link to the parent table, so that we can insert the Primary Key of the parent table to the child table.
The header element (MemHeader) in the XML document does not have a unique ID. If we are optimistic and assume that we could make each header element unique by combining SrcCode, EID and MemID elements, then the process will be easy. Here is a piece of sample code that performs the requested operation based on the above assumption.
DECLARE @x XML
SELECT @x = '
<Member>
<MemHeader>
<SrcCode>B</SrcCode>
<EID>100000</EID>
<MemID>HSG-200</MemID>
</MemHeader>
<AttributeList>
<Item>
<Groupname>NAME</Groupname>
<AttributeName>First</AttributeName>
<Currentvalue>Lee</Currentvalue>
</Item>
<Item>
<Groupname>NAME</Groupname>
<AttributeName>Middle</AttributeName>
<Currentvalue>Marvin</Currentvalue>
</Item>
<Item>
<Groupname>Name</Groupname>
<AttributeName>Last</AttributeName>
<Currentvalue>Bruised</Currentvalue>
</Item>
</AttributeList>
</Member>
<Member>
<MemHeader>
<SrcCode>C</SrcCode>
<EID>120202</EID>
<MemID>CTX-300</MemID>
</MemHeader>
<AttributeList>
<Item>
<Groupname>NAME</Groupname>
<AttributeName>First</AttributeName>
<Currentvalue>Barry</Currentvalue>
</Item>
<Item>
<Groupname>NAME</Groupname>
<AttributeName>Middle</AttributeName>
<Currentvalue>M</Currentvalue>
</Item>
<Item>
<Groupname>NAME</Groupname>
<AttributeName>Last</AttributeName>
<Currentvalue>Humphries</Currentvalue>
</Item>
<Item>
<Groupname>ADDRESS</Groupname>
<AttributeName>Line1</AttributeName>
<Currentvalue>40 Fore Street</Currentvalue>
</Item>
</AttributeList>
</Member>'
-- header table
DECLARE @MemberHeader TABLE (
MemHeaderID INT IDENTITY, -- primary key
srcCode VARCHAR(20),
EID VARCHAR(20),
MemID VARCHAR(20) )
-- detail table
DECLARE @AttributeList TABLE (
AttListID INT IDENTITY, -- primary key
MemHeaderID INT, -- foreign key
GroupName VARCHAR(20),
AttributeName VARCHAR(20),
CurrentValue VARCHAR(20) )
-- insert into header
INSERT INTO @MemberHeader( srcCode, Eid, MemID )
SELECT
x.value('SrcCode[1]','VARCHAR(20)'),
x.value('EID[1]','VARCHAR(20)'),
x.value('MemID[1]','VARCHAR(20)')
FROM @x.nodes('/Member/MemHeader') mh(x)
-- insert into details
INSERT INTO @AttributeList( MemHeaderID, GroupName, AttributeName, CurrentValue )
SELECT
h.MemHeaderID,
d.GroupName,
d.AttributeName,
d.CurrentValue
FROM (
SELECT
h.value('(MemHeader/SrcCode)[1]','VARCHAR(20)') SrcCode,
h.value('(MemHeader/EID)[1]','VARCHAR(20)') Eid,
h.value('(MemHeader/MemID)[1]','VARCHAR(20)') MemID,
d.value('Groupname[1]','VARCHAR(20)') AS GroupName,
d.value('AttributeName[1]','VARCHAR(20)') AS AttributeName,
d.value('Currentvalue[1]','VARCHAR(20)') AS CurrentValue
FROM @x.nodes('/Member') mh(h)
CROSS APPLY h.nodes('AttributeList/Item') al(d)
) d
INNER JOIN @MemberHeader h ON
h.srcCode = d.srcCode
AND h.Eid = d.Eid
AND h.MemID = d.MemID
-- test the value
SELECT * FROM @MemberHeader
/*
MemHeaderID srcCode EID MemID
----------- -------------------- -------------------- --------------------
1 B 100000 HSG-200
2 C 120202 CTX-300
*/
SELECT * FROM @AttributeList
/*
AttListID MemHeaderID GroupName AttributeName CurrentValue
----------- ----------- -------------------- -------------------- --------------------
1 1 NAME First Lee
2 1 NAME Middle Marvin
3 1 Name Last Bruised
4 2 NAME First Barry
5 2 NAME Middle M
6 2 NAME Last Humphries
7 2 ADDRESS Line1 40 Fore Street
*/
However, it could happen that the combination of SrcCode, EID and MemID is not unique. If that is the case, the above code will not work. We will get incorrect results if that is the case. What is the next best option?
One way to handle this is to run a loop over the Header element and insert each element (header and details) one by one. Writing a loop over the XML elements is pretty easy. I have explained it in XML Workshop XVII - Writing a LOOP to process XML elements in TSQL at www.sqlservercentral.com. So the next approach we will take would involve the following.
- run a loop over all the <member> elements
- For each element
- Insert the information to header table
- Take the value of the new identity key inserted to server by calling SCOPE_IDENTITY()
- Insert the value to the detail table (along with the primary key of the server
- next element
- end loop
Let us see how the code looks like.
DECLARE @x XML
SELECT @x = '
<Member>
<MemHeader>
<SrcCode>B</SrcCode>
<EID>100000</EID>
<MemID>HSG-200</MemID>
</MemHeader>
<AttributeList>
<Item>
<Groupname>NAME</Groupname>
<AttributeName>First</AttributeName>
<Currentvalue>Lee</Currentvalue>
</Item>
<Item>
<Groupname>NAME</Groupname>
<AttributeName>Middle</AttributeName>
<Currentvalue>Marvin</Currentvalue>
</Item>
<Item>
<Groupname>Name</Groupname>
<AttributeName>Last</AttributeName>
<Currentvalue>Bruised</Currentvalue>
</Item>
</AttributeList>
</Member>
<Member>
<MemHeader>
<SrcCode>C</SrcCode>
<EID>120202</EID>
<MemID>CTX-300</MemID>
</MemHeader>
<AttributeList>
<Item>
<Groupname>NAME</Groupname>
<AttributeName>First</AttributeName>
<Currentvalue>Barry</Currentvalue>
</Item>
<Item>
<Groupname>NAME</Groupname>
<AttributeName>Middle</AttributeName>
<Currentvalue>M</Currentvalue>
</Item>
<Item>
<Groupname>NAME</Groupname>
<AttributeName>Last</AttributeName>
<Currentvalue>Humphries</Currentvalue>
</Item>
<Item>
<Groupname>ADDRESS</Groupname>
<AttributeName>Line1</AttributeName>
<Currentvalue>40 Fore Street</Currentvalue>
</Item>
</AttributeList>
</Member>'
DECLARE @MemberHeader TABLE (
MemHeaderID INT IDENTITY, -- primary key
srcCode VARCHAR(20),
EID VARCHAR(20),
MemID VARCHAR(20) )
DECLARE @AttributeList TABLE (
AttListID INT IDENTITY, -- primary key
MemHeaderID INT, -- foreign key
GroupName VARCHAR(20),
AttributeName VARCHAR(20),
CurrentValue VARCHAR(20) )
DECLARE @cnt INT, @max INT, @MemHeaderID INT
SELECT @cnt = 1
SELECT @max = @x.query('<e>
{ count(/Member) }
</e>'
).value('e[1]','int')
WHILE @cnt <= @max BEGIN
-- insert Header
INSERT INTO @MemberHeader( srcCode, Eid, MemID )
SELECT
x.value('(MemHeader/SrcCode)[1]','VARCHAR(20)'),
x.value('(MemHeader/EID)[1]','VARCHAR(20)'),
x.value('(MemHeader/MemID)[1]','VARCHAR(20)')
FROM @x.nodes('/Member[position()=sql:variable("@cnt")]') mh(x)
SELECT @MemHeaderID = SCOPE_IDENTITY()
-- insert Details
INSERT INTO @AttributeList( MemHeaderID, GroupName, AttributeName, CurrentValue )
SELECT
@MemHeaderID,
d.value('Groupname[1]','VARCHAR(20)'),
d.value('AttributeName[1]','VARCHAR(20)'),
d.value('Currentvalue[1]','VARCHAR(20)')
FROM @x.nodes('/Member[position()=sql:variable("@cnt")]') mh(x)
CROSS APPLY x.nodes('AttributeList/Item') al(d)
SELECT @cnt = @cnt + 1
END
-- test the values
SELECT * FROM @MemberHeader
/*
MemHeaderID srcCode EID MemID
----------- -------------------- -------------------- --------------------
1 B 100000 HSG-200
2 C 120202 CTX-300
*/
SELECT * FROM @AttributeList
/*
AttListID MemHeaderID GroupName AttributeName CurrentValue
----------- ----------- -------------------- -------------------- --------------------
1 1 NAME First Lee
2 1 NAME Middle Marvin
3 1 Name Last Bruised
4 2 NAME First Barry
5 2 NAME Middle M
6 2 NAME Last Humphries
7 2 ADDRESS Line1 40 Fore Street
*/